Need more help from previous "Is this possible?..." post

[Dan B]

Sorry for repeat post...can't get this to work....

DizzyKid,
Thanks for your reply.
I tried your suggestions and it duplicated the city state and zip on lines
three and four. It also put a square box, instead of a space between the
city, state and zip.
Here is my original formula for lines 3 and 4:
=IF(ISNA(VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,4,FALSE)),0,VLOOKUP($I$16,'C:\Work\[customer
s from caselle.xls]Sheet1'!$A$2:$G$710,4,FALSE))


=IF(ISNUMBER(MATCH($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,5,0)&", "&
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,6,0)&" "&
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,7,0),"")

Here are your formulas for lines 3 and 4 as I have them entered into the
spreadsheet:
=IF(ISNUMBER(MATCH($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0))="",VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,5,0)&",
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,6,0)&"
"&VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,7,0),VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0
)),"")


=IF(ISNUMBER(MATCH($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0))="",VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,5,0)&",
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,6,0)&"
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,7,0)),"")

Did I do something wrong?

Thanks Again,
Dan

 

[Dan B]

never mind, I got it working

Sorry for repeat post...can't get this to work....

DizzyKid,
Thanks for your reply.
I tried your suggestions and it duplicated the city state and zip on lines
three and four. It also put a square box, instead of a space between the
city, state and zip.
Here is my original formula for lines 3 and 4:
=IF(ISNA(VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,4,FALSE)),0,VLOOKUP($I$16,'C:\Work\[customer
s from caselle.xls]Sheet1'!$A$2:$G$710,4,FALSE))


=IF(ISNUMBER(MATCH($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,5,0)&", "&
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,6,0)&" "&
VLOOKUP($I$16,'C:\Work\[customers from
caselle.xls]Sheet1'!$A$2:$G$710,7,0),"")

Here are your formulas for lines 3 and 4 as I have them entered into the
spreadsheet:
=IF(ISNUMBER(MATCH($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0))="",VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,5,0)&",
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,6,0)&"
"&VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,7,0),VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0
)),"")


=IF(ISNUMBER(MATCH($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,4,0))="",VLOOKUP($I$16,'[customers from
caselle.xls]Sheet1'!$A$2:$G$709,5,0)&",
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,6,0)&"
"&VLOOKUP($I$16,'[customers from caselle.xls]Sheet1'!$A$2:$G$709,7,0)),"")

Did I do something wrong?

Thanks Again,
Dan

Starting with the formula previously posted by Mr Grove, and assuming these
columns: 1 num, 2 name, 3 add1, 4 add2, 5 city, 6 state, 7 zip:

These formulas placed in four consecutive rows in the same column will
produce a "mailing label" of sorts.

=IF(ISNUMBER(MATCH($I$16,Sheet1!$A$2:$A$710,0)),VLOOKUP($I$16,Sheet1!$A$2:$G=IF(ISNUMBER(MATCH($I$16,Sheet1!$A$2:$A$710,0)),VLOOKUP($I$16,Sheet1!$A$2:$G=IF(ISNUMBER(MATCH($I$16,Sheet1!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,Sheet1"&VLOOKUP($I$16,Sheet1!$A$2:$G$710,7,0),VLOOKUP($I$16,Sheet1!$A$2:$G$710,4,0=IF(ISNUMBER(MATCH($I$16,Sheet1!$A$2:$A$710,0)),IF(TRIM(VLOOKUP($I$16,Sheet1

!$A$2:$G$710,4,0))="","",VLOOKUP($I$16,Sheet1!$A$2:$G$710,5,0)&",
"&VLOOKUP($I$16,Sheet1!$A$2:$G$710,6,0)&"
"&VLOOKUP($I$16,Sheet1!$A$2:$G$710,7,0)),"")

Hopefully that's what you're looking for.
Chris