Food list - Formula to calculate nutritional macros

Justified1103

New member
Joined
Aug 21, 2013
Messages
3
Reaction score
0
Points
0
Hi there,

I wonder if this can be done,

For my nutritional plan i have a section for each meal I'm going to have, eg. meal 1, meal 2, meal 3
And i have a section where i have inputted certain protein sources, carbohydrate sources with nutritional information per 100 g/ml.

Is there a way via data validation to allow a list of food groups to be selected from a list and then the amount in g/ml to be entered in like an input box, then via reference to the info from the sources section, having it populate the nutritional information.

For eg. if i have chicken that has 20g of protein per 100g and for meal 1 i select -> chicken and i input 200g .. the column under "protein" will show "40g" ..

i have attached an excel doc of the 2 sections to show you what i mean.. is this possible to do?

Thanks very much
 

Attachments

  • demo macros formula.xlsx
    10.6 KB · Views: 91

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
For the Data Validation, create a list of all the sources available and make it a Named Range. I put it in column T in the attached.

You can make the list Dynamic (DNR), so that you can add and remove sources at will.

Go to Formulas tab, then Define Name from the Defined Names section, then enter a name like: Sources and apply formula: =OFFSET(Sheet1!$T$1,1,0,COUNTA(Sheet1!$T:$T)-1,1) click Ok to accept

Now select G3:G6 and go to Data|Validation and select List from the Allow menu, then apply formula: =Sources

Finally, in L3 apply formula:

=IFERROR(INDEX(B:B,MATCH($G3,$A:$A,0))*$J3/100,0)


copied across and down the table.

Now make selections, enter amounts and see the results.
 

Attachments

  • Copy of demo macros formula.xlsx
    11.2 KB · Views: 52

Justified1103

New member
Joined
Aug 21, 2013
Messages
3
Reaction score
0
Points
0
That is amazing! thank you very much!!!

one minor querie, if i want to make heading in the list, i have tried to, but is there a way to centralize them in the select list?
to also have a text in the amount column that says "enter amount" then when u click on it, it goes blank for you to input.

Iv attached the file
 

Attachments

  • Copy of Copy of demo macros formula.xlsx
    11.8 KB · Views: 46

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
I am not quite sure what you are after here. Why do you want the headings in the drop down?
 

Justified1103

New member
Joined
Aug 21, 2013
Messages
3
Reaction score
0
Points
0
Its not like a compulsory thing, but just so things are in order. but you have helped me a TON!!! thank you so very much

Ill definitely reccomend this forum to any of my friends!
 
Top