0

I am trying to learn how to refer "dynamically" to a cell from another sheet given a textual condition.

I have the following scenario:

Example

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).

enter image description here

In sheet "A", I refer to each food with a subsequent column of quantity as shown below. enter image description here


Objective

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".


Question:

  1. 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 INDIRECT and ADDRESS together (such as in this example)?

  2. 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)?

New contributor
user929304 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
6
  • So just to get the value of Table 1 B2 onto sheet A, what would be the syntax?
    – Solar Mike
    2 days ago
  • @SolarMike Honestly, to get started, yes that is really what I'm looking for. Later, I intend to extend it such that given each "quantity" selected in sheet A, the preceding cell "ingredient" is taken from sheet B to know the nutritional coefficient.
    – user929304
    2 days ago
  • So why try to do the complete calculation? Just work in steps. First value, then find the correct result then do the calc.
    – Solar Mike
    2 days ago
  • so the value of B3 on sheet 2 can be shown on sheet 1 with Sheet 2::Table1 1::B3
    – Solar Mike
    2 days ago
  • 1
    Then use a lookup or index match to get the particular value.
    – Solar Mike
    2 days ago

1 Answer 1

1

You could use xlookup function. If table containing calories is named Nutritional_value and have structure you provided in screenshot this formula works: xlookup takes value from column Ingridient 1 in current table, looks for same value in first column (A) in Nutritional_value table and returns corresponding value from Calories column; then it's multiplied with value in column Quantity 1 and divided with 100 (to get correct calories):

XLOOKUP(Ingridient 1 'Monday - breakfast';Nutritional_value::A;Calories)×Quantity 1 'Monday - breakfast'÷100

However, this approach is not scalable very well (you must do the same for other ingridients in a meal) and if you want make any analysis it's poor design. IMHO better would be to have one row per ingredient (data table) and analysis should be made in Pivot Table

1
  • Thanks a lot! This is most interesting. I absolutely agree with you about the scalability, and indeed I want to do lots of analysis overtime so it's good to have a starting structure conducive for that. Would you be so kind to slightly flesh out in more detail on your last recommendation of row per ingredient and pivot table? I am not sure how that would pan out, any dummy example would be most appreciated.
    – user929304
    yesterday

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .