Help with drop down name to autofill address

[Nathan Tidd]

I haven't been able to figure this out or find the right article for
help so I thought I would post. I have an invoice I'm creating. I
would like to have a drop down list in it to select a company name and
when selecting it, the contact name, address, state and zip will
autofill in the invoice.

So I have an invoice with B11 where the company name goes, from there
B12 - B14 contains the contact name, address, state and zip. Now I
have another sheet named "customers" with the information is seperate
cells like below:

A1 B1 C1 D1 E1 F1
CompanyName Contact Address City State ZIP

So how can I get it so that I select a "companyname" from a drop down
list and it autofills the fields below it with data from another
sheet? I've been trying with vlookup but cannot get it to work (I
just think that I don't understand how it works).

Bonus: I'm using Excel 2007 and am Excel illiterate.

Thanks for the assistance.

 

[vishal007]

First of all you will need to have a database of the company names and other
required details in a worksheet in the file. The database will serve as data
source for the lookup function that we will use. The database can be
something like this in a woksheet. Suppose sheet name to be "Database"
A B C D
E F
1 Tiger Consultants John Smith 561 street NewYork Newyork
51475

Then in the other worksheet you can use the drop down for company name and
then use the vlookup function in the other columns where you want the data to
be autopopulated. For eg: in the next sheet

A1 B1
C1
Tiger Consultants =vlookup(A1,Database!A1:F4,2,0) =vlookup(A1,Database!A1:
F4,2,0)

The vlookup function will look for the company name selected from the
dropdown in the database in the Database worksheet and then return the
corresponding data. You can refer to the help topic for the vlookup function
to understand the parameters used in the function. Try this and see if it
works for you. It can be demonstrated more accurately if we work on a file.

 

[Nick Hodge]

Nathan

To fill the dropdown drop a combobox from the forms toolbar (Developer
tab>Controls group>Form controls). (You will need to add the developer tab
to the ribbon via Excel Options...>Popular).

Right click the control and select Format Control

In the input range box enter the address of the list of Company names on the
other sheet (e.g Sheet2!$A$1:$A$100) and set the Cell link to the cell under
the control (e.g. $A$1). You should now have a combobox with the Company
Names in and when you select from the list it should show the index number
in the cell under the control

Now in the contact cell on your sheet enter

=INDEX(Sheet2!$A$1:$F$100,$A$3,2)

This should return the item in the data range on sheet2, the number of rows
down in the linked cell (under the control) and then return the data in the
second column of the data range (The contact name)

Hope that makes sense?
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Nathan Tidd]

Ok, I think I've got it working for the most part although I'm not
sure I understand how. Nick, is there a way to combine multiple
values in one cell (city state, zip)?

Here are the values:
city = =INDEX(Customers!$D$1:$F$11,$B$11,2)
state = =INDEX(Customers!$E$1:$F$11,$B$11,2)
zip = =INDEX(Customers!$F$1:$F$11,$B$11,2)

Also was wondering how to incorporate an address line 2 in this, if
there is no field it returns 0, is there a way around this by having a
blank cell or shifting everything up if it returns 0?

Thank you for your time!