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

[wako182774]

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]

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.

 

[gleemonex69]

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.

 

[gleemonex69]

Forgot the pics

Liquor
http://l.bitcasa.com/tZep.Z3z

Tare
http://l.bitcasa.com/tZep.Z3z

 

[Purple Youko]

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]

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