Excel – How to auto-populate from one sheet to another

microsoft excel

Here is the scenario. For ease, I will use a Calorie log as my example.

Worksheet1 contains all food consumed for an entire day. Worksheet2 is an index of the foods consumed.

For example, in Worksheet2 I have:

A1 Name of Food
B1 Serving Size
C1 Calories
D1 Protein
E1 Carbs
F1 Fat
...
A24 Banana
B24 1 banana
C24 100
D24 5
E24 5
F24 5

In Worksheet1 I have what food I ate on any specific day with the totals added up for calorie counting.

What I would like to be able to do, is input the value of the food into A1 in Worksheet1 and have it automatically fill out the information from Worksheet2.

For example, in Worksheet1 I'd like to have:

A1 = Cell Value of Food in Worksheet 2, i.e. A24.

I would then like B2, B3, B4, B5, B6, B7 to auto-populate with the information from Worksheet2.

I know how to call a value from another spreadsheet, but I don't know how to call a value from another spreadsheet based on a value in worksheet1.

Best Answer

User376123, I have set up an example to accomplish what you want.

I have Sheet1 which contains my list of data:

enter image description here

In Sheet 2 I have formulas set up to do the math and looking up of what I ate. I input what I ate and the servings of that thing that I ate in columns A and B:

enter image description here