Excel : Use data in one table of Excel to calculate values in another

0
votes

I am trying to build a simple food nutrient calculator on excel that I can share with others.

I have one sheet with list of food items and nutrient information per 100 grams:

Item Weight (g) Calories Protein Carbs Fat Fiber Sugar
Rice White 100 130 2.7 28 0.3 0.4 0.1
Rice Brown 100 111 2.6 23 0.9 1.8 0.4
Almonds 100 579 21.15 21.55 49.33 12.5 4.35
Peanuts 100 567 25.8 16.13 49.24 8.5 4.72
Cashew 100 553 18.22 30.19 43.85 3.3 5.91
Walnuts 100 654 15.23 13.71 65.21 6.7 2.61
Pistachios 100 560 20.16 45.32 27.17 10.16 7.66
Hazelnuts 100 628 14.95 16.7 60.75 9.7 4.34
Rice Basmati 100 121 3.54 25.22 0.38 0.4 0.05

I want another sheet where the user logs amount of each food item he or she consumes for the day. I want the item names to be from the drop-down . Rest of the columns need to be calculated.

Time Item Amount (g) Calories Protein Carbs Fat Fiber Sugar
10:00 AM (item name dropdown) 200 (formula) (formula) (formula) (formula) (formula) (formula)

Can you explain how to achieve this?

I can create the a list from the Item Name column in the sheet1 using Data Validation, but how to get the rest of the column values from Sheet1?

2
excelexcel-formula

2 Answers

1
votes

Assuming that source data is in A1:H10 and then second table data is in K1:S2 then in cell N2 i.e. for Calories formula would be:

=INDEX($C$2:$H$10,MATCH($L2,$A$2:$A$10,0),MATCH(N$1,$C$1:$H$1,0))*($M2/INDEX($B$2:$B$10,MATCH($L2,$A$2:$A$10,0)))

where

=INDEX($C$2:$H$10,MATCH($L2,$A$2:$A$10,0),MATCH(N$1,$C$1:$H$1,0)) matches values from calories table and then multiplier is calculated by dividing the total quantity by reference amount in column B using $M2/INDEX($B$2:$B$10,MATCH($L2,$A$2:$A$10,0).

0
votes
  1. Format as table. (Select table > Home > Format as Table)
  2. Give a name. (Table > Table Name). I chose "FoodTable".
  3. On the new sheet create the table with headers. I added a few more to make it easy to debug the formulas:
Time Item Weight (g) Reference Weight Calories Original Protein Original Carbs Original Fat Original Fiber Original Sugar Original Calories Protein Carbs Fat Fiber Sugar
10:00 AM Rice Brown 200 100 121 3.54 25.22 0.38 0.4 0.05 242 7.08 50.44 0.76 0.8 0.1

  1. Add data validation to Item column (Data > Data Validation > Validation Criteria > Allow > List. You can choose the source to highlight the item column)

  2. In the Reference Weight column you can enter the following formula to find Weight in FoodTable: =INDEX(FoodTable[Weight (g)],MATCH([@Item],FoodTable[Item]))

  3. Repeat the same for other "Original" Columns.

  4. Fill in the Calories with the formula: =[@[Weight (g)]]*[@[Calories Original]]/[@[Reference Weight]]. Repeat for other columns.

  5. Optionally, hide the "Original" columns.