I am trying to learn how to refer "dynamically" to a cell from another sheet given a textual condition.
I have the following scenario:
There are two sheets labelled "A" and "B".
In sheet "B" I have a table of 12 rows and 4 columns. Each row there is labelled with a nutritional label, such as "Oats", followed by columns giving corresponding calories, Carbs, etc (see image below).
From sheet "A", I am trying to calculate the calroies of a meal, so for each ingredient I want to multiply its quantity with its equivalent calories per 100 grams as provided in sheet "B".
Requirement for the formula: the ability to refer to a cell of another sheet based on a textual condition from the current cell. For example, I want to be able to say the quantity at hand corresponds to "Oats" as the column preceding "Quantity 1" is "Oats". So intuitively, I have 50 grams of Oats (sheet A) that I want to multiply by the calories of Oats (taken from sheet B per 100g). So I'm trying to express the formula such that it automatically takes the preceding cell to quantity and treats its text, here "Oats", as the cell reference to sheet B.
This is the first time I am trying to do dynamic referencing. My attempt (not working) has so far been along the lines of using
INDIRECT function to refer to corresponding cell of sheet B given the text "Oats". So I have tried:
A::Table 1::E2 * B::Table 1::Oats&"::B2"
But it gives an error. Even if it worked, this would still not be what I really wanted to achieve, because my ideal intention is the ability to the preceding cell of B2 from sheet B without having to explicitly specify "Oats".
Where am I going wrong with my syntax in the formula in referring to another sheet (B) and taking its cell corresponding to 2nd column of the row "Oats"? Should I somehow be using the functions
ADDRESStogether (such as in this example)?
Is there a way to generalize this, where we no longer have to specify even the food, but we'd just select the quantity and the food would automatically be taken as the cell preceding it (row-wise)?