Relative novice at Excel, looking for help with drop down list schema

[NephroMD]

Hi everyone,

I'm conducting a research project and using Excel to collect raw data. The data we're looking at includes collecting the nutritional information for different infant formulas. My current layout has different rows for each day of the week and columns to assess nutritional intake (e.g. "N" is "Formula Used", "O" is "Formula Protein (grams/mL)", "P" is "Formula Carbohydrate (grams/mL)", etc.) and I'd like to figure out a way where I can create a drop down list of formulas in column N such that when I select a particular formula it will auto-populate the pre-defined nutritional information in columns O, P, and so on. I truly appreciate your help with this.

Brian

[BadlySpelledBuoy]

Hi Brian,

This is simple enough to do.
Could you post a sample workbook. It just needs to show your layout and show a couple of samples of the formulas and nutritional information.
BSB.

[FDibbins]

Hi, welcome to the forum

To create a dropdown, 1st, you need to have a list of items to choose from. If that list is small, 3-4 items, you can type them directly into the DD window. This can make editing the list of items (or adding to them later), a cumbersome process.

The other option is to create a list of items somewhere in your workbook (same sheet, or on another sheet), and then use that as the list for the DD. Either way, this is how you would set it up...

1. Highlight the range you want to apply the DD to
2. Select the Data tab and click Data Validation
3. in the window that appears, under ALLOW, click the DD and select List
4. for the SOURCE, either type in each item, separated by a comma, or point to the list you created IN the workbook (you could also give the list a range name and refer to that)
Click OK, and you will see the small DD arrow in the active cell, test by clicking that and you should see a list of items

[NephroMD]

Hi FDibbins,

That was helpful for creating the basic drop-down list. Is there a way to get more advanced and have it such that when I click on an item from the drop-down list it auto-populates associated cells or numeric values with it? For example, each infant formula has an associated protein, carbohydrate, fat, etc. numeric value and I'd like those to auto-populate in certain cells when I drop-down the respective formula.

[BadlySpelledBuoy]

The easiest way is to have a table on a separate sheet that holds the infant formula names in a column and all the related attributes, protein, carbs etc. in subsequent columns.

You then use VLOOKUP or INDEX/MATCH to feed that information in based on the infant formula name selected in the drop down.

BSB.

[Pepe Le Mokko]

Perhaps http://www.contextures.com/xlDataVal01.html

[FDibbins]

As described by BSB, the next step is actually not really part of the drop-down as such. You would use a vlookup (or index/match) which would do a search based on the cell contents of the DD.

(My point being that you dont need the DD for the VLOOKUP to work, you could just as easily type the criteria into the cell - Not saying to not use the DD, just trying to show that they can operate without each other)

So, lets assume you have a table like this, with your DD in column A...

	A	B	C	D
1	name	Thing1	Thing2	Thing3
2	aa	123	234	345
3	bb	133	244	355
4	cc	143	254	365

Then in another table, you have all teh different values for each name and its associated thing...

	F	G	H	I
1	name	Thing1	Thing2	Thing3
2	aa	123	234	345
3	bb	133	244	355
4	cc	143	254	365

Then in B2, copied down and across, you would use...
=VLOOKUP($A2,$F$2:$I$4,2,0)

[NephroMD]

I'm going to attach my workbook to see if anyone can help with the formulas I need to do the same INDEX/MATCH function in two separate columns:

On the "Home" spreadsheet, I would like for there to be a drop down list in column D of the IV fluid names in the IV fluids spreadsheet. In column F of the "Home" spreadsheet I want to have it such that upon doing the drop down list in column D the corresponding IVF sodium value (mg/mL) automatically is filled in.

In column H of the "Home" spreadsheet, I'd like a drop down list of the different TPN types in column A of the TPN spreadsheet, and for their corresponding values in columns B through F of the TPN sheet to autofill in columns J through N of the "Home" spreadsheet.Formula Project Workbook v1.1.xlsx

[FDibbins]

Thanks for the file

You have 2011, so you can probably skip step 1 and just reference the range directing.

1. Create a range name for each drop-down list that you have.
For instance, I created a named range IVFliudName for the IV fluild list
2. Highlight the range you want to apply the drop-down to, select Data tab/data Validation/List type in an = and copy/type in the range name (IVFliudName in this case) =IVFliudName (with excel later than 2007 you can just reference the range)
3. to pull in the relevant value, use this, copied down...
=IF(D2="","",VLOOKUP(D2,'IV Fluids'!$A$2:$B$5,2,0))

You can use this same approach for th other DD's as well. If you have a problem, give me a shout again

[NephroMD]

Can you explain the parts of the formula bracketed by the VLOOKUP? What do I have to do to get multiple columns to fill in automatically with values that are associated with a drop down list item?

[newdoverman]

I filled in your Home worksheet as I understood your requirements.

[NephroMD]

Thanks so much!

[FDibbins]

Sure

=IF(D2="","",VLOOKUP(D2,'IV Fluids'!$A$2:$B$5,2,0))
This is 1st of all testing to see if there is a v alue in D2...
=IF(D2=""....
If there is not, it keeps the cell looking empty...=IF(D2="","",
If there is, the vlookup triggers...
=IF(D2="","",VLOOKUP(D2,'IV Fluids'!$A$2:$B$5,2,0))
In english that says...
If D2 is empty, show nothing, otherwise, find the contents of D2 in the range 'IV Fluids'!$A$2:$B$5, return the value in the 2nd column, and I want an exact match (the ,0 at the end)

However (there always has to be a "however", right?) I just noticed that you will have multiple columns for each "category" (IV, TPN etc), so I have changed that formula to something that will manage multiple columns *easier*

If you look at your attached WB, you will see that I "dummied up" extra columns on you IV sheet, and I have used a different formula in your Home sheet...
=IF(D2="","",INDEX('IV Fluids'!$B$2:$D$5,MATCH($D2,IVFliudName,0),MATCH(Home!E$1,'IV Fluids'!$B$1:$D$1,0)))

Dont freak out, its not as bad as it looks
It starts out with that same test I mentioned above...
=IF(D2="","",
and then I brought in a close relative of VLOOKUP() called the INDEX/MATCH combo
This works as follows...
=INDEX(range, row number, column number)
The range is straight-forward, it is all the data (excluding the headings) in your table. You could include the row/column headings as well, but that would be redundant here)

For the row number, we use the MATCH function, which works like this...
=match(what to find, where to find it (a single column range), exact match) sound familiar, kinda looks a bit like the vlookup, right? well it works the same way)
the "where to find it" has to be a single column-range that contains what you are looking for)

We can use the exact same thing for the column number, we swing the MATCH around...
=match(what to find, where to find it (a single row range, exact match)

So we end up with a range of data to find something in. The 1st MATCH tells us which row to look in and the 2nd MATCH tells us which column to look down - and where they intersect is our answer

[FlameRetired]

Can you explain the parts of the formula bracketed by the VLOOKUP? What do I have to do to get multiple columns to fill in automatically with values that are associated with a drop down list item?

Edit: Ford's is the same as mine. I posted before I refreshed the screen.

I referenced the list in the formualas tab, defined the drop-downs in column A of the TBS tab, and entered this formula in B2 of that same sheet:

Please Login or Register  to view this content.

Once entered, drag the fill handle across the columns as far as necessary. Then drag down as far as you like. The N/A errors can be suppressed with this modification:

Please Login or Register  to view this content.

Hope this helps.