How can I create a Drop-Down List to populate other cells?

wako182774

New Member
Joined
Oct 18, 2009
Messages
5
Ok, guys, new to this forum and I need some help with a business invoice. I know how to create a drop down list. But what I want to do is create a Drop-Down list with customers names, say cell A11. After selecting a customer name from the list, It would be able to populate the address in cells A12 and A13. Is this possible?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

wako182774

New Member
Joined
Oct 18, 2009
Messages
5
use VLOOKUP.

Thanks for the help.
I looked up VLOOKUP. It looks really complicated and I am not sure where to even start.

If on Sheet 2, for instance, I have A9 (Customer Name), A10 (Customer Address), and A11 (Customer Address Cont.), and so one for hundreds of customers including a space cell between (ex. A12).

First, how will I be able to create a drop down list in Sheet 1, Cell A11, with just Customer Names (A9, A13, A17, ....)?

Second, if I select A9, how do I get Sheet 2, A10 and A11, to populate in Sheet 1, A12 and A13?
 

Mr Stanford

Board Regular
Joined
Sep 12, 2009
Messages
142
First, how will I be able to create a drop down list in Sheet 1, Cell A11, with just Customer Names (A9, A13, A17, ....)?

Second, if I select A9, how do I get Sheet 2, A10 and A11, to populate in Sheet 1, A12 and A13?

You would need a list of all the names in a column, then use data validation to create the dropdown. Then use vlookup the find the address etc according to the name selected
 

crimson_b1ade

Well-known Member
Joined
Sep 27, 2008
Messages
1,557

ADVERTISEMENT

...If on Sheet 2, for instance, I have A9 (Customer Name), A10 (Customer Address), and A11 (Customer Address Cont.), and so one for hundreds of customers including a space cell between (ex. A12)...


Firstly, your data is not in the appropriate format (e.g. List Format) to create a drop down. You'll want to have your names, and addresses going down separate columns (not the same column). In addition, you shouldn't have blank rows in your list. Example: Names in Column A, Address in Column B.

Second, to create cell drop downs on Sheet1 (for example...and lets say your Name is in Column A on Sheet2), click Data then Validation on the menu bar. In the Data Validation popup ("DV"), change the "Allow" criteria to "List" then in the source ref box put in =Sheet2!$A$1:$A$10 (this assumes you have ten rows of data).

Now to look up the address based on the value selected from the dropdown you'll use vlookup. Let's assume the dropdown was in cell A1 on Sheet1 and you want to populate the address in Sheet1 in cell B1. So to achieve this, in Cell B1 of Sheet1 you'll put the following function: =VLOOKUP(A1,Sheet2!$A$1:$B$10,2,False)

The above function simply means..Lookup cell A1 name value, in Sheet2 column A, and return the 2nd column of the range (column B in this case), and the last part of the function we'll put "False" because we want an exact name match.
 
Last edited:

wako182774

New Member
Joined
Oct 18, 2009
Messages
5
Second, to create cell drop downs on Sheet1 (for example...and lets say your Name is in Column A on Sheet2), click Data then Validation on the menu bar. In the Data Validation popup ("DV"), change the "Allow" criteria to "List" then in the source ref box put in =Sheet2!$A$1:$A$10 (this assumes you have ten rows of data).

Now to look up the address based on the value selected from the dropdown you'll use vlookup. Let's assume the dropdown was in cell A1 on Sheet1 and you want to populate the address in Sheet1 in cell B1. So to achieve this, in Cell B1 of Sheet1 you'll put the following function: =VLOOKUP(A1,Sheet2!$A$1:$B$10,2,False)

The above function simply means..Lookup cell A1 name value, in Sheet2 column A, and return the 2nd column of the range (column B in this case), and the last part of the function we'll put "False" because we want an exact name match.

Ok, and thanks for you help again, but I am still having a little problem.
On Sheet2, A13:A17 has Customer Names, B13:B17 has Address 1, and C13:C17 has Address 2 (The contents are dummy informations that I am using for Test). Also, I used Define Name for cells A13:A17 as Customer_Name.

In Sheet1, I have A11 as the Drop-Box using Allow: List, Ignore Blank, In-cell dropdown, and Source: =Customer_Name. The Drop Down List works fine, showing customer names only.

Here is the problem. For cell A12, I would like Address 1 returned based on the Customer Name selected. This is the string entered: =VLOOKUP(A13,Sheet2!$A$13:$C$17,2,FALSE). I am getting a #N/A return.

Two things to note, not sure if they have any effect.
1. I am using Excel 2007.
2. On Sheet2, A12 reads "Customer Name," B12 reads "Address 1," and C12 reads "Address 2.)

Thanks for any help again.
 

wako182774

New Member
Joined
Oct 18, 2009
Messages
5

ADVERTISEMENT

Never mind, thanks again. I got it to work using the Formula function within Excel.
 

Purple Youko

Active Member
Joined
Sep 17, 2004
Messages
265
Here is the problem. For cell A12, I would like Address 1 returned based on the Customer Name selected. This is the string entered: =VLOOKUP(A13,Sheet2!$A$13:$C$17,2,FALSE). I am getting a #N/A return.
The first id in a vlookup is the cell in which the reference value is found.
In your case the list which is in cell A11 so your formula should be
=VLOOKUP(A11,Sheet2!$A$13:$C$17,2,FALSE)
 

wako182774

New Member
Joined
Oct 18, 2009
Messages
5
The first id in a vlookup is the cell in which the reference value is found.
In your case the list which is in cell A11 so your formula should be
=VLOOKUP(A11,Sheet2!$A$13:$C$17,2,FALSE)

Your right, That is what I had wrong. Thanks for the help, will definetly have more questions in the future.
 

gleemonex69

New Member
Joined
Dec 9, 2010
Messages
37
Hmm, ok so this might help me make an inventory spreadsheet for my bar, but how would I get it to auto-populate several cells instead of one. For example, if I did a drop down list to select a brand of liquor. Then I want it to auto-populate Category (type of liquor), bottle size (ounces), bottle size (metric) and tare weight (ounces). I have two pages Liquor and Tare Weights, with the data on Tare Weighs. The drop down list would be in say A2 and category would be B2, bottle size (ounces) C2, bottle size (metric) D2 and tare weight on E2 all on the Liquor page. I tried the above formula, but it only worked for category and I can't seem to get the others working. Thanks in advance.
 
Last edited:
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,605
Messages
5,765,385
Members
425,281
Latest member
tmoreira001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top