Combine lists in one big list

[strawberry1972]

I've gone through a number of posts to try and find a solution but no joy yet.

Basically I am trying to automatically create a weekly shopping list.

I have a drop down menu for the meals but after they are selected I want to get the list of ingredients for all the meals in a new sheet to be my shopping list. Get ingredients for first recipe copy them to new sheet then get the ingredients of the next meal and add that on and so on.

When the list is complete I then need to sort it so it combines 2 tomatoes for one recipe with 3 tomatoes for another to only show 5 tomatoes.

I can't really think where to start on this so don't have an example to post up.

Help is much appreciated.

 

[Peter_SSs]

strawberry1972

Not sure that I will have a suggestion for this, but how many meals do you anticipate would be available in the drop down?

 

[strawberry1972]

I have 7 meals on it - one for each day of the week.

I also have the ingredients for each meal on another sheet using the meal name as the header eg. 'Chili Con Carne' or 'One Pot Pork Casserole'

 

[Peter_SSs]

I have 7 meals on it - one for each day of the week.

I also have the ingredients for each meal on another sheet using the meal name as the header eg. 'Chili Con Carne' or 'One Pot Pork Casserole'

One of us is mis-understanding the other. If you only have 7 meals in the drop-down and you are choosing 7 meals for the week, then your shopping list will always be the same (unless you are choosing the same meal more than once in the week). Am I missing something?

 

[strawberry1972]

Sorry Peter, I have around 120 meals in total

 

[strawberry1972]

Any ideas folks?

 

[Peter_SSs]

I have been giving this some thought but as yet cannot think of any reasonably easy way to attack it. If a macro solution presented itself (not that one has occurred to me yet), would that be acceptable?

How are your meals and ingredients set out? Could you post a sample of part of your sheet that shows say 3 or 4 meals and their ingredients?

Small samples of a sheet can be shown on the board using Colo’s HTML Maker:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
or Excel jeanie:
http://www.excel-jeanie-html.de/index.php?f=1

 

[strawberry1972]

Here is my weekly meals :-

Book2
	A	B	C	D
1	Day	Dinner
2	Saturday	Pizzaandgarlicbread
3	Sunday	Roastchicken
4	Monday	Chickenfajitas
5	Tuesday	Porkmeatballs&spaghetti
6	Wednesday	BakedPotatoeswithtuna&salad
7	Thursday	BeefCasserole
8	Friday	Quiche
9	Saturday	PorkChops
10	Sunday	Salmon&DillFishcakes,NewPotatoes&Veg
11	Monday	Sweet&SourChickenwithRice
Sheet1

The Ingredients look like this :-

Book2
	D	E	F	G
1	INGREDIENTS
2	Pizzaandgarlicbread
3	100g	flour
4	3	eggs
5	1tsp	driedyeast
6	100g	cheese
7	2tbsp	tomatopuree
8	2tbsp	oil
9	1	ciabattabread
10	2cloves	garlic
11	50g	butter
12	Roastchicken
13	1	largechicken
14	2	lemons
15	2tsps	herbs
16	1clove	garlic
17	1kg	potatoes
18	500g	carrots
19	2	onions
Sheet1

and the shopping list would combine all the ingredients and add together items with the same name as I said earlier.

I appreciate you taking this on board, thanks for the links to paste spreadsheets too, hope it all works ok.