Auto-populate list on wider selection

Occasional Visitor

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:

cdoug982_0-1673146062500.png

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

cdoug982_1-1673146144011.png

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

 

1 Reply

@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.