Returning a Table After Selecting a Value in Drop Down List

[rtuttle123]

Hi,

I was looking to see if anyone knew a way to return a table of information by selecting single value from a drop down list. My issue is that the values in the table will be different with each single value so I am not sure how vlookup could work with that. Think of selecting one value from a drop down and returning a recipe with a list of ingredients in one column and the amount in the next column. I would appreciate any help.

Thanks,

Robert

 

[RasGhul]

Can you post some sample data?

 

[rtuttle123]

[TABLE="width: 500"]
<tbody>[TR]
[TD]123456[/TD]
[TD]APPLE PIE RECIPE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]INGREDIENT[/TD]
[TD]QUANTITY[/TD]
[TD]UNIT OF MEASURE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PIE CRUST[/TD]
[TD]1[/TD]
[TD]BOX[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]APPLES[/TD]
[TD]6[/TD]
[TD]CUPS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SUGAR[/TD]
[TD].75[/TD]
[TD]CUPS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FLOUR[/TD]
[TD]2[/TD]
[TD]TBSP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CINNAMON[/TD]
[TD].75[/TD]
[TD]TSP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]SALT[/TD]
[TD].25[/TD]
[TD]TSP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]NUTMEG[/TD]
[TD].125[/TD]
[TD]TSP[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]LEMON JUICE[/TD]
[TD]1[/TD]
[TD]TBSP[/TD]
[/TR]
</tbody>[/TABLE]

I would like to know if there is a way to store info in a table like this on one sheet and have a list on another sheet with all the numbers (123456) that when I select the number (123456) the corresponding data will be displayed in next to the drop down selection. I know you can do this with vlookups and it will return one or multiple results in a row but when you try to get it to look at the same number in different rows it doesn't look to the next row. I wasn't sure if there was a way or a formula that would return a table to a corresponding value like (123456). My hope would be to store multiple tables on a single sheet to draw from. Any help would be appreciated.

Thanks,

Robert

 

[RasGhul]

Hi,

Note how the recipe number is copied for each row you want to return, copy this formula across and then down.

Expand the ranges to suit your data. If you get zero in the table as per below, you can turn these off with

File Tab/Options/Advanced/Display Options for this worksheet/un-tick Show a zero in cells that have zero value.

Book1
	A	B	C	D
1	123456	APPLE PIE RECIPE
2	123456	INGREDIENT	QUANTITY	UNIT OF MEASURE
3	123456	PIE CRUST	1	BOX
4	123456	APPLES	6	CUPS
5	123456	SUGAR	0.75	CUPS
6	123456	FLOUR	2	TBSP
7	123456	CINNAMON	0.75	TSP
8	123456	SALT	0.25	TSP
9	123456	NUTMEG	0.125	TSP
10	123456	LEMON JUICE	1	TBSP
Sheet1

Book1
	A	B	C
2	Recipe
3	123456
4
5	INGREDIENT	QUANTITY	UNIT OF MEASURE
6	APPLE PIE RECIPE	0	0
7	INGREDIENT	QUANTITY	UNIT OF MEASURE
8	PIE CRUST	1	BOX
9	APPLES	6	CUPS
10	SUGAR	0.75	CUPS
11	FLOUR	2	TBSP
12	CINNAMON	0.75	TSP
13	SALT	0.25	TSP
14	NUTMEG	0.125	TSP
15	LEMON JUICE	1	TBSP
Sheet2

Cell Formulas
Range	Formula
A6	{=IF(ROWS(Sheet2!$A$6:A6)>COUNTIF(Sheet1!$A$1:$A$10,Sheet2!$A$3),"",INDEX(Sheet1!B$1:B$10,SMALL(IF(Sheet1!$A$1:$A$10=Sheet2!$A$3,ROW(Sheet1!A$1:A$10)-ROW(Sheet1!A$1)+1),ROWS(Sheet2!$A$6:A6))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[rtuttle123]

Thanks so much RasGhul. This formula worked perfectly. I appreciate the help.

 

[RasGhul]

You're welcome thanks for the feedback.