MULTIPLE ROW DROPDOWN LIST
 

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.

MULTIPLE ROW DROPDOWN LIST
 

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.

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"JustAnOilMan" wrote:

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.

MULTIPLE ROW DROPDOWN LIST
 

Sorry, A6's formula was missing the second ampersand...it should be:
=IF(A3<"",VLOOKUP(A3,EntireList,4,0)&" "&VLOOKUP(A3,EntireList,5,0),"")
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"KC" wrote:

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.

--
Please remember to indicate when the post is answered so others can benefit
from it later.


"JustAnOilMan" wrote:

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.

MULTIPLE ROW DROPDOWN LIST
 

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
--
grizz


"JustAnOilMan" wrote:

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.