New Link After Changing Cell Location - please help :)

[Antje888]

How can I automatically update the cell-link location?

For my Restaurants Food Cost Calculations I refer from my recipe-sheet to the price on my ingredients-sheet. By doing this I just have to adjust the price only once to have all my recipes (70+) prices updated.

I sort the ingredients on the ingredients-sheet in an alphabetical order. Now I added some ingredients and the location of the cells has shifted down.

The link on my recipe-sheet refers still to the old cell though, now showing me an incorrect price.

Is there any way to have LibreOffice Calc to automatically grab the original cell, after location has changed?

Thanks so much in advance for your answers!

===This is only a bump to put the question on top again for other visitors to the site. (Lupp)===

[Lupp]

This comment is made in return to a comment on an answer I had tried by the questioner.
Quoting: “Sorry but his is not an answer to my question. Please read my question”

I read your question. Without knowing the formulas your sheet actually contains I cannot decide if your problem has a solution at all in the sense you hope to find one. To avoid a probably fruitless to-and-fro of questions and questions in return, I described and exemplified a way how your task -as far as I understood it- can reliably be solved.
Technically spoken your ingredients sheet is a lookup table, and lookup tables should be used looking-up values.
To avoid blocking other contributors from trying answers suiting you better, I will now delete my attempted answer.

[Antje888]

Ok, thank you, I appreciate your help. I just had it linked to certain cells. Lookup tables would be too long since I have about 160 different ingredients divided into 6 categories. I just tried to link the cell that contains the price directly with ’ ‘=’ and then selecting the cell. Then I added another ingredient so the linked-to cell went from B7 to B9. Now the link was still set to B7 instead of B9

[Lupp]

What you describe is what I assumed, and the result is what most(?) users expect and want. Independent of what users want or not, it’s the actual behaviour of spreadsheets for decades now.
Partly ambiguities and misunderstandings are due to the limitations of language (not a specific one).
The term “cell” e.g. is used in at least 3 basically different meanings, and even documentation and help texts sometimes make a mess of it.
For your specific use-case you want the formula to referenc a cell in the sence of “position in the cell-grid”, and therefore as an object that never can move. The actual object referred to by your formula is not identical with the area at a row/column position in the grid, but something that has such a position (and many properties/attributes) and can move elsewhere.
Insertion/deletion of rows / columns / a cellRange basically is treadted as a process moving cells - and the term “delete” again is ambiguous…

[Lupp]

Your Del key e.g. doesn’t delete cells, but clear contents if applied in a spredsheet.
Referring to a cell requires the usage of a kind of name, and the current name (the address) of the cell is made up from its current position in the grid. If this position is changed (by dragndrop e.g.), its “identity” is protected by automatic adaption of its address in all the formulas explicitly referring to it. There are complications, of course…
For these substantial reasons there is no solution to your question, except probably one using specific tools in an error-prone way. At the same time I describe such an as-if-solution I would seriously dissuade from using it. That’s not my way.
Do it by a kind of lookup as I already demonstrated (state of the art for good reasons) - or wait for someone else giving you the as-if-solution.
Regards
Old attachment again:
ask248853RecipesAndIngredientsCalculations_1.ods