Auto-populate list on wider selection

[cdoug982]

Hi all,

Super specific one - I'm trying to alter an Excel template ("weekly meal planner and grocery list"). The grocery list is only pulling information based on 7 entries from the meal planner tab. What I wanted to do was expand the information pulled to be across 7 rows & 3 columns (for breakfast, lunch & dinner instead of the single dinner column that the template originally holds) but I cannot for the life of me figure out how I can change the index or lookup array to include.

Formula to pull items from recipe if the meal is entered into the meal planner table:
=IFERROR(INDEX(IngredientsBlanks,SMALL((IF(LEN(IngredientsBlanks),ROW(INDIRECT("1:"&ROWS(IngredientsBlanks))))),ROW(A1)),1),"")
Formula for amount of each ingredient:
=IFERROR(VLOOKUP('Shopping list'!$B3,DataAnalysis!$U$8:$W$301,3,FALSE),"")


Original meal plan format:

What I'd like the shopping list to populate based on:

I'm confused and I really don't want to have to have 3 seperate workbooks for breakfast lunch & dinner.

[#2]

@cdoug982 

From an image it's virtually impossible to diagnose what the limits are. It would be helpful if you could let us know what template that is, where you found it. Ideally, paste a link to the actual file. I went looking briefly and there are quite a few results when one searches for "Meal Planner" among the Excel templates on the Microsoft site.

I will also say that templates often have unfortunate limitations that can frustrate users. So it could well be something inherent in the design, not your fault. But help us help you by giving more information so that we could find the actual template.

Or you could just post a copy of yours on OneDrive or GoogleDrive and paste a link here that allows us to look at it and edit it.

[#3]

@mathetes 

The link to the template would be this: 

https://create.microsoft.com/en-us/template/weekly-meal-planner-and-grocery-list-0a470abd-cbab-429b-...

I have the same problem, if I want to expand the table, the ingreedients of my manually added columns are not schown in the grocery list.

[#4]

@Maria5830 

Would you be willing to post a copy of the spreadsheet as you've modified it? Put it on OneDrive or GoogleDrive and paste a link here that grants edit access.

(Because I'm not a user of this template, I'd otherwise have to try to re-create your circumstances, which would take time, and probably. be off-base anyway.  I do do the cooking in my household, so I'm interested in seeing how this works, but it would be very helpful if I see your attempts at using it for starters.)

[#5]

@cdoug982 
I am trying to do the exact same thing so just wondering if you find a solution? I would like to include at list 2 recipes per day or Breaky, Lunch and Dinner.

Thankyou

[#6]

@Maria5830 I am trying to do the exact same thing so just wondering if you find a solution? I would like to include at list 2 recipes per day or Breaky, Lunch and Dinner.

Thankyou

[#7]

@Lyra1 A typical example of a template that is designed to be used as is or not at all. It's packed with formula's referencing all over the place and the driving force of it is stashed away in three hidden sheets. Then there are tons of named ranges cross-referencing each other. 

Tinkering with it required expanding tables in hidden sheets, adjusting named ranges and manually adjust some hard-coded direct references.

Then it proved easiest to expand the planner table to 21 rows (7 breakfast, 7 lunch and 7 diner) and 3 columns (Day, Alternative 1, Alternative 2).

To test this revised version, I created one silly recipe for each category containing only one ingredient. And it seems to work. But see for yourself.

You can hide the three sheets that are not color-coded to avoid accidental changes to them as these would, most likely, break the entire template.