MULTIPLE ROW DROPDOWN LIST

[JustAnOilMan]

Thanks for any help in advance!
I would like to have a 4 line billing address dropdown list of multiple
companies as follows.
ABC Company
1234-9th st
Anywhere USA
77689
Is it possible to do this in Excel 2003? The dropdown can contain the whole
address or just the company name but I want to display the address as above
in 4 separate rows.

 

[KC hotmail com>]

Sure, this isn't too hard at all. On another worksheet (maybe Sheet2) you'll
want to have a master list of clients in column A, with their corresponding
address line 1 in column B, address line 2 in column C, city in column D,
state in column E, and zip in column F.

Now select any cell in that list and hit Ctrl+L (that's the Control key and
the L key at the same time). This will convert your list into an Excel Data
Table List. As you add more data to the list, the list will auto-expand.

Next, highlight A2 through the last row used in A (let's say A2:A100) and
give it a name (such as ClientLookup), then highlight A2:F100 and give it a
name (such as EntireList). It would behoove you to keep this list sorted by
client name.

Now back on Sheet1, let's say you're desired drop down will be in cell A3.
Go to cell A3 and select Data > Validation, limit to a List, and under Source
type this formula:
=ClientLookup

So now you have a drop-down list in A3 of clients. In A4, here is your
formula:
=IF(A3<>"",VLOOKUP(A3,EntireList,2,0),"")
Then in A5 you'll want address line 2 (if there is a line 2)
=IF(A3<>"",IF(ISERROR(VLOOKUP(A3,EntireList,3,0)),"",VLOOKUP(A3,EntireList,3,0)),"")
In A6 you'll want city & state:
=IF(A3<>"",VLOOKUP(A3,EntireList,4,0)&" "VLOOKUP(A3,EntireList,5,0),"")
Finally in A7 is the zip code:
=IF(A3<>"",VLOOKUP(A3,EntireList,6,0),"")

You'll get errors if it cannot find an address line 1, city, state, and/or
zip, but a missing address line 2 will not generate an error.

 

[KC hotmail com>]

Sorry, A6's formula was missing the second ampersand...it should be:
=IF(A3<>"",VLOOKUP(A3,EntireList,4,0)&" "&VLOOKUP(A3,EntireList,5,0),"")

 

[grizzly6969]

If you need to display your address in this manner in a drop down you may
have to wrap text in cell
right click -- format cell -- alignment -- wrap text

 

[Keith Cunningham]

Grizz

This is an excellent solution for supplying an invoice address and works perfectly.

My only problem is that I have 5 columns in my master list, but some of the customers have an address which fills only 4 columns and when I pick these particular addresses in the invoice the missing info shows up as an 0.

Is there any way I can ammend your code so that the 0 does not show and only the actual address is shown.