I am trying to simplify my shipping documents within Excel. I have created a
drop down list by cities of my shipping destinations. I need to populate the
entire shipping addresses that are in another sheet within the workbook. It
can be in the column or row next to the drop down list. The addresses will
also be multiple cells, not just one cell.

Any help would be appreciated.

So, for any given City, there is only one 'ship-to' address? That is, the
destination in Los Angeles is always the same business/street address in Los
Angeles, while the destination in Atlanta is always to the same
business/street address in Atlanta?

"cra88" wrote:

I am trying to simplify my shipping documents within Excel. I have created a
drop down list by cities of my shipping destinations. I need to populate the
entire shipping addresses that are in another sheet within the workbook. It
can be in the column or row next to the drop down list. The addresses will
also be multiple cells, not just one cell.

Any help would be appreciated.

Yes, that is correct.

"JLatham" wrote:

So, for any given City, there is only one 'ship-to' address? That is, the
destination in Los Angeles is always the same business/street address in Los
Angeles, while the destination in Atlanta is always to the same
business/street address in Atlanta?

"cra88" wrote:

I am trying to simplify my shipping documents within Excel. I have created a
drop down list by cities of my shipping destinations. I need to populate the
entire shipping addresses that are in another sheet within the workbook. It
can be in the column or row next to the drop down list. The addresses will
also be multiple cells, not just one cell.

Any help would be appreciated.

Ok, the easiest way to get this done with VLOOKUP() is to have a table with
the city names in the left-most column of a table. The rest of the
information can be in pretty much any order, but we'll assume it's laid out
this way:

A B C D E

1 city ST zip Bus.Name street

..
..
..
20 city ST zip Bus.Name street

This is on a sheet named [ShippingInfo], so the address of the table area is
'ShippingInfo'!$A$1:$E$20

While you could have the dropdown list with city names on several sheets, we
will assume it's just on one, a sheet named [Drop Down Sheet]. And on that
sheet, the drop down is in cell C3.

For any place you need to get part of the address you can put in a formula
like this:

=VLookup('Drop Down Sheet'!$C$3,'ShippingInfo'!$A$1:$E$20,#,False)
where ,#, is a number from 1 to 5 for our table:
,1, will return the city name again
,2, would return the state
,3, would return the zip code
,4, would return the business name, and
,5, would return the street address.

It would help you if you used a Named Range for the table. To do that you'd
go to the sheet it is on and select the entire table from A1 to E20 and then
up in the little area where it normally shows the address of the cell you are
in, type a name for the table (has to be a single word and you MUST terminate
it with the [Enter] key), such as ShippingTable

Then you can substitute ShippingTable in the formulas instead of the sheet
name and cell range of the table. If you need to add new cities later, add
them as new rows WITHIN the existing list (after row 1 and before what is now
row 20) and the formulas will continue to work without you having to go back
and revise them to account for new entries. Formula would look something
like this (again, substitute the appropriate number where I have put in the #
symbol):

=VLookup('Drop Down Sheet'!$C$3,ShippingTable,#,False)

Hope this helps.


"cra88" wrote:

Yes, that is correct.

"JLatham" wrote:

So, for any given City, there is only one 'ship-to' address? That is, the
destination in Los Angeles is always the same business/street address in Los
Angeles, while the destination in Atlanta is always to the same
business/street address in Atlanta?

"cra88" wrote:

I am trying to simplify my shipping documents within Excel. I have created a
drop down list by cities of my shipping destinations. I need to populate the
entire shipping addresses that are in another sheet within the workbook. It
can be in the column or row next to the drop down list. The addresses will
also be multiple cells, not just one cell.

Any help would be appreciated.