Forum Discussion

ISCHOW's avatar
ISCHOW
Copper Contributor
Dec 18, 2023

Linking data between sheets

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...
excel
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 18, 2023

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

 

 

Resources