Linking data between sheets

ISCHOW 

To make sure your formulas dynamically adjust when you add or sort new ingredients in the shopping list, you can use structured references or named ranges. Here's how you can do it:

Method 1: Using Structured References

1. Convert your data to an Excel Table:

Select the range of your shopping list.

Press Ctrl + T to convert it to a table.

Ensure the "Create Table" dialog box has the option "My table has headers" checked.

2. Use Structured References:

Update your formulas on the other sheets to use structured references.

For example, if you want to refer to the "Pineapple Juice" quantity, use: ='Shop List'[[#Headers],[Pineapple Juice]].

3. Formula Example:

=INDEX('Shop List'[Pineapple Juice], MATCH([@Ingredient], 'Shop List'[Ingredient], 0))

This formula assumes your recipe sheet is also set up as a table, and [@Ingredient] refers to the ingredient in the current row of your recipe table.

Method 2: Using Named Ranges

1. Create Named Ranges:

Select the range of your shopping list.

In the "Formulas" tab, click on "Create from Selection" in the "Defined Names" group.

Choose "Left column" if your data has headers, or "Top row" if not.

2. Use Named Ranges in Formulas:

When writing formulas on the other sheets, refer to the named ranges.

For example, if you named the range for "Pineapple Juice" as PineappleJuice, use: =PineappleJuice.

Using either of these methods ensures that your formulas will adjust automatically when the shopping list is updated or sorted.

Method 3: Create a dynamic reference

If you prefer not to use structured references or named ranges, you can create a dynamic reference using functions. Here is an example:

Suppose your shopping list is in the range A1:B100 on the 'Shop List' sheet, and the headers are in the first row. If you want to find the quantity of "Pineapple Juice," and the name of the ingredient is in cell A1 of your recipe sheet, you can use the following formula:

=INDEX('Shop List'!$B$1:$B$100, MATCH(A1, 'Shop List'!$A$1:$A$100, 0))

Choose the method that best fits your workflow and spreadsheet structure.

The text, steps and formulas were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

ISCHOW 

"Pineapple Juice" is the unique identifier.  Ideally data should be held in Tables and referenced using structured references.  That is both because the references are meaningful and because structured references adjust as data is added.  Also sorting a table sorts all the fields, making it hard to trash the data in the manner that traditional approaches allow.

In my opinion, the idea of referencing 'Pineapple Juice' by its location

'Shop List'!M$66 is an abomination that has no place in any computing environment.  As you may gather, I am not a great fan of traditional spreadsheet practice, effective though it may be in the hands of an expert user.  

Typically the resulting formulas are longer than a 'normal' formula but they are more informative, e.g.

= XLOOKUP(
      [@Ingredient], 
      CostTbl[Ingredient], 
      CostTbl[Price] * [@Quantity] / CostTbl[Quantity],
     "not listed"
  )