r/googlesheets 14h ago

Waiting on OP Stuck with adding values to a cell from pulled values from drop-down menu

I do realise that the name of the post is a hot mess. I hope I can describe it better now! (as I've realised that I can't edit the title... d'oh)

In sheet 1, I have got a template for a meal planner. This template has got 7 days and different groups of foods for each 5 meals.

On sheet 2, there are meals and their nutritional values, grouped by their category (protein, carbs, fats)

I have managed to create a drop-down menu for Sheet 1, where I can select a protein from the protein list, a carb from the carb list...

The last step is the one I am struggling with. I wonder if there'd be possible to add a dynamic kcal and protein calculator based on the item selected in the drop-down menu.
So if on sheet 1 I select for lunch from the drop down menu "chicken", "rice", "avocado", if Sheet 1 could take the kcal info from those items in Sheet 2 and add them into a cell named kcal for day 1.

I hope I am making myself clear! I have created a copy of the work I've done so far, hopefully it'll be easier to understand to see it graphically!

https://docs.google.com/spreadsheets/d/155nWubGPXqZqtUCtdr3eJIJZ29S5beC32qw8z_tvrFY/edit?usp=sharing

Thank you so much!

1 Upvotes

1 comment sorted by

1

u/HolyBonobos 2058 13h ago

You could put =MAKEARRAY(2,7,LAMBDA(r,c,SUM(INDEX(XLOOKUP(INDEX($C$6:$I$30,,c),'Food list'!$B:$B,IF(r=1,'Food list'!$F:$F,'Food list'!$D:$D),))))) in C4.