Excel macro for shipping addresses

Microsoft Office excel 2007 - upgrade
November 19, 2010 at 11:47:29
Specs: Windows 7
I have 40 ship to locations that I use frequently in my quote template. Is there a way to create a menu / drop down of the City's name - click it and it fills in 5 cells of information vertically, Company,
City, State, Zip

As this is a template it can be in the same workbook just on another tab

See More: Excel macro for shipping addresses

Report •

November 19, 2010 at 12:08:34
You could set up a VLOOKUP Table with the values in the first column being the list that is used for the Drop Down. Let me explain...

Let's say I create this table:

           A                 B                C             D          E
1   Akron, OH 11111      CompanyName 1     Address 1    P-Number 1   Name 1
2   Olean, NY 22222      CompanyName 2     Address 2    P-Number 2   Name 2
3   Manse, FL 33333      CompanyName 3     Address 3    P-Number 3   Name 3

In the cell where the City goes (e.g. A7), I would create a Data Validation Drop-Down list that is populated with the values in A1:A3

In A5 I would put this formula to pull the CompanyName associated with the city chosen from the Drop Down.

=VLOOKUP(A7, $A$1:$E$3, 2, 0)

For the Street, I'd put this in A6:

=VLOOKUP(A7, $A$1:$E$3, 3, 0)

The same concept would be used in A8:A9 for the Phone Number and Name.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •
Related Solutions

Ask Question