Linking data between sheets

[ISCHOW]

I am building a workbook to calculate food cost.  On Sheet 1, I have a shopping list with price and quantity of ingredients.  On the following sheets I have recipes with the food cost associated with each dish.  Everything works great until I add a new set of ingredients to my list and the sort the list.  Then the formulas on the following sheets do not match the ingredients on the shopping list.       

EX. This is the location of pineapple juice on the shopping list ='Shop List'!M$66.  After I added and sorted the new ingredients. The new location for pineapple was  ='Shop List'!M$107.  Although the following sheets with formulas linking to pineapple were still ='Shop List'!M$66 when I would like them to automatically change to ='Shop List'!M$107.   How do you do this?

I feel this can be accomplished by adding a unique identifier column to reference from.   Any help would be appreciated. 

[#2]

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

[#3]

Thank you. I will try to implement those formulas later today. Thanks for two options as well. @NikolinoDE 

[#4]

@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"
  )