Bring nutrition values across from food selected from dropdown list

[NoniJ]

Hi everyone,

I do hope someone can help me please.

I'm putting together a daily food intake/nutrition spreadsheet for my 14yo basketball son, so he can track what he's eating and maintain a healthy balanced plan.

I have all our regularly consumed food and nutrition values maintained in a sheet called FOOD.

I have his daily food consumption maintained in another sheet called DIARY

In this example , I have cut/paste his food consumption from the FOOD sheet into the DIARY sheet (cumbersome and must be a better way).

Within the DIARY sheet, I'd like a dropdown selection of all foods (with sub-headings) for him to select, THEN bring the associated nutrition values across as per the food he selected.

Can someone please assist me with this?

I have tried using the INDIRECT function however, I need a separate sheet for each food item which I believe there MUST be an easier way to do this.

I have also created worksheets, separating each sub-heading (BREAD/CEREAL/DAIRY, etc) copying data from the FOOD list with nutrition content) - not sure if this helps.

Worksheet called PASTA

I appreciate your time and hope someone can provide instructions of how to bring the Nutrition value through (which corresponds with food item selected from the Dropdown List).

With thanks,
Noni


Minisheets

Eamon's Meal Plan.xlsx
	J
15
FOOD

Eamon's Meal Plan.xlsx
	E
48
DIARY

Eamon's Meal Plan.xlsx
	C
27
PASTA

 

[NateSC]

If all your food is on the Food sheet it makes it easier vs multiple sheets.
- Have just your food and headers in one column. (If A, just delete the word "Food" on the Food tab.)
- Create a drop down list by going to Data, and in the Data Tools select Data Validation
- Under Allow, select List
- Under Source, either select Column A on the Food tab or type the following =Food!$A:$A
- Make sure Ignore Blank and In Cell Dropdown both have a check beside them
- Click OK
- Then just copy them down the page where needed

To do the lookup, use XLookup (since you are using Office 365). You will set the first row independently, but then you can copy it down the page. See equations below:

Book1
	B	C	D	E	F	G	H
4	Breakfast	Qty	CaL	Protein	Sugar	Carbs	Fat
5	multigrain	2	187	9	1.7	27	3.6
Diary

Cell Formulas
Range	Formula
C5	C5	=XLOOKUP(B5,Food!A:A,Food!B:B,"")
D5	D5	=XLOOKUP(B5,Food!A:A,Food!D:D,"")
E5	E5	=XLOOKUP(B5,Food!A:A,Food!E:E,"")
F5	F5	=XLOOKUP(B5,Food!A:A,Food!F:F,"")
G5	G5	=XLOOKUP(B5,Food!A:A,Food!G:G,"")
H5	H5	=XLOOKUP(B5,Food!A:A,Food!H:H,"")

Cells with Data Validation
Cell	Allow	Criteria
B5:B9	List	=Food!$A:$A

I hope this helps.