Food list - Formula to calculate nutritional macros

[Justified1103]

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

 

[NBVC]

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.

 

[Justified1103]

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

 

[NBVC]

I am not quite sure what you are after here. Why do you want the headings in the drop down?

 

[Justified1103]

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!

 

[NBVC]

Ok Great