List Formulation Question

[hogfan1978]

Hi,

First post here, so if this is in the wrong spot, I apologize.

Background: I'm trying to make an easy to use grocery list using Excel. I want to make it where the first column (approximately 30 cells in the first column) has a drop down menu where my wife can choose the main course, such as "roast" or "hamburger". I have done this successfully.

What I want to do next is where I'm having trouble:

When she selects "roast" or "hamburgers" from that first column, I want another list to be populated (probably on the next sheet, but it doesn't really matter) with all the ingredients it takes to make a roast or hamburger, or whatever else she selects from the first column.

Any idea how to do this? Warning: I am not proficient in VBA....which might be my downfall....

Thanks!

[FDibbins]

Hi, welcome to the forum

assuming your data looks something like this (cols D:F)...

	A	B	C	D	E	F
1	aa	aa1		aa	bb	cc
2		aa2		aa1	bb1	cc1
3		aa3		aa2	bb2	cc2
4		aa4		aa3	bb3
5		aa5		aa4	bb4
6				aa5	bb5
7					bb6
8					bb7
9					bb8
10					bb9

A1 = the "recipe"
B2=IF(INDEX(OFFSET($D$1,1,MATCH($A$1,$D$1:$F$1,0)-1,20,1),ROW())=0,"",INDEX(OFFSET($D$1,1,MATCH($A$1,$D$1:$F$1,0)-1,20,1),ROW()))
copied down

Note, "name in A1 needs to match the headings in D1:F1

[newdoverman]

You could use a formula like this...adjust the cell references to suit placement on your worksheet.

Please Login or Register  to view this content.

[hogfan1978]

Thanks a lot for the replies.

FDibbins, I used your methodology. It works well. Now I have another question: Now that I have all the ingredients for each recipe in individual columns, how do I combine all those recipes into one continuous list (or column), without blank spaces between them? Keep in mind that each recipe has a different number of ingredients, which means a different number of cells. Not sure how to get the final total ingredient list to not have blank cells between them...

newdover - thanks for the reply!

hog

[FDibbins]

My thoughts where that you would have a column for each recipe, with each column having the name of the recipe as the heading. Then under each heading would be the list of ingredients.

So instead of, in my sample table in post #2...
D1=AA, it would contain Apple Pie. Then all the cells under it would contain the different ingredients
D2=BB, it would contain tomato soup. Then all the cells under it would contain the different ingredients

etc

[hogfan1978]

Basically here's the output columns I have thanks to your formulation:

Hamburgers/ Roast
Meat / Roast
Cheese / Carrots
Seasoning / Potatoes
Buns / Seasoning
Onion / Onion

This is great so far, but I'd really like the above to look like:

Meat
Cheese
Seasoning
Buns
Onion
Roast
Carrots
Potatoes
Seasoning
Onion

Basically combining all the ingredients into one grocery list.

Thanks,
Ryan

[hogfan1978]

No way to do this ^^^^^ without VBA?

Thanks.

[FDibbins]

Im not sure why (or how) you would have 2 different meals in your list?

[hogfan1978]

Let me try to explain it better. The spreadsheet is set up so that it compiles the grocery list for the entire month. My wife selects the main course for each day of the month and the spreadsheet compiles the entire month's groceries in 1 printable list.

I've got each recipe's ingredient list in separate columns currently. Now I just want to compile them into 1 single grocery list for the whole month. Does that make sense?

[hogfan1978]

So, I was able to solve my combining lists issue. I ended up using the following array formula (the C1:C500 is where the combined list will copy into excel):

=IFERROR(INDEX(Recipe1, ROWS(C1:$C$500)), IFERROR(INDEX(Recipe2, ROWS(C1:$C$500)-ROWS(Recipe1)), IFERROR(INDEX(Recipe3, ROWS(C1:$C$500)-ROWS(Recipe2)-ROWS(Recipe1),""))) etc, etc.

This combined the recipes into a vertical list.

Then I used a simple boolean formula to get all the blank spaces to equal "", instead of 0.

Then I used the following formula in a new column to copy the grocery list and display it without blanks:

=INDEX(Listwithblanks, SMALL(IF(ISBLANK(Listwithblanks), "", ROW(Listwithblanks)-MIN(ROW(Listwithblanks))+1), ROW(A1)))

This was more than likely the long way to get this done, but it does work. We can now go in and select a meal from the menu of recipes for each day of the month and the spreadsheet will automatically spit out a list with all ingredients for all the recipes. I even had Excel display the list in store order for our local grocery store. lol.

Thanks for all the help - couldn't have done it without this website!

hog