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?
 

Purple Youko

Active Member
Joined
Sep 17, 2004
Messages
265
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.
There are a few different ways to achieve this.
You could either use multiple vlookup calls using the same input value from your dropdown list with each vlookup calling data from a different column in your reference table.
or
You could do the whole thing in VBA code using a worksheet event that is triggered on changing the current value in your dropdown list.

The VBA method may seem more complicated but for anyone who has a little experience in writing code like that it is actually pretty easy. I could help you build something if you like.

it just occurred to me that there may be a third way.
Making a custom in-cell function that works in a similar way to vlookup but returns values in multiple columns. This method really is a little more complicated though. Making custom functions can be... interesting.
 
Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

gleemonex69

New Member
Joined
Dec 9, 2010
Messages
37
Ok, here is what I have and need. The first pic shows the Liquor page and the second pic shows the Tare Weights page.

Liquor


Tare Weights


I know how to create a dropdown list, but I want that drop down list to auto-populate the rest of the empty cells in the Liquor page. Or maybe a VBA would be easier, but I don't have any experience with VBA.
 
Last edited:

Purple Youko

Active Member
Joined
Sep 17, 2004
Messages
265
As I said the easiest way is just to use a different vlookup table for each of the columns.
I threw together a simple spreadsheet using the tow images that you posted.
Just added the top 4 entries. You just need to expand the lookup table address in the functions to make it read the whole of the data in the Tare weights tab

you can download it from my web space
http://kuro-tejina.com/code/liquor example.xlsx

I could also do it with VBA but sometimes that causes problems with sharing files so i stuck with normal cell functions for now.
 

figment222

New Member
Joined
Mar 6, 2015
Messages
46
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?

I see others have recommended VLookup, but I think that is a terrible formula that is very, VERY touchy. If the columns for your source data ever changes, you will have to reassign them in every one of your formulas. what you're asking for help with sounds very similar to what I've been doing on multiple scales.

What you need is Index/Match

I have a sheet that contains a list of every one of our customers (around 1500 lines). there are columns for company name, account number, address, phone number, etc.

this can be tedious to sort through and sometimes, it is nice to get a snapshot for all the relevant info on one particular customer.

I created a sheet where I can input the customer's account number into a field and all of the other fields will automagically populate (address, sales info, etc)

The formula would look something like this, but don't get intimidated, because it is actually much easier to me than VLOOKUP ever was and the best part is that if you move your columns around for your source data, the formula will adjust on its own... automagically.

=INDEX('Account Data'!A:A,MATCH($B$2,'Account Data'!$B:$B,0))

'Account Data' is the sheet where the list of data is contained. That is where I'm pulling information from.

=INDEX('Account Data'!A:A,MATCH($B$2,'Account Data'!$B:$B,0))
This section is "what information do I want to put into this field?" In this case, I wanted the company name in the cell where I put this formula. Column A of my account data sheet is where all of the company names are. This tells the Index function "this is the range of cell that contains the information I want you to find"

=INDEX('Account Data'!A:A,MATCH($B$2,'Account Data'!$B:$B,0))
Match is a function you can use to tell INDEX which company name from the source data to populate. In this case, I will be putting the account number into cell B2. In my source data, Column B contains the account numbers for each of the customers. So, I'm telling the formula to match whatever is in B2 with column B in my source data and then populate whatever is in column A for that line of data.

the 0 in the formula tells the formula to find an exact match to B2 in the source data, rather than finding the next closest (in which case you would put a 1, but I want an exact match, so I always use 0)

So, B2 has the customer ID that I would manually type in.
B3 is where I put the formula and when I type in an account number into B2, the company name pops up, according to the company name that is next to that particular account number in my source data. I can do the same thing to get the address, phone number, etc. I would be using the same formula with slightly different column references.

Trust me when I say IndexMatch is better than VLOOKUP. This article might help explain it better than I did.
Why INDEX MATCH is Better Than VLOOKUP
 

Watch MrExcel Video

Forum statistics

Threads
1,109,411
Messages
5,528,621
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top