[SOLVE]
Hello, I have a master ingredients cost sheet with a price in it. I have another Recipe sheet where I reference that price data. If I insert a row in the master ingredient sheet it also changes the data in the recipe sheet. I should note these are separate files. Is there a way to add a row and/sort without breaking the link? Thank you.
Please upload an ODF type sample file here.
Hello
I’d perform a lookup of the ingredient’s price from the ingredient added to the recipe instead of directly linking to the Price/g cell in Ingredients file. Example: Your recipe for the Potato Wedges shows Potato as its first ingredient. If you want to know the Price /g from the Ingredients file, don’t directly link (which finally results in your question) but perform a lookup using the following formula (I have copied the files to my /tmp directory - of course you need to adapt to your directory structure):
=VLOOKUP(A4;'file:///tmp/Ingredients.ods'#$Sheet1.A$5:H$1000;8;0) (into cell C4)
(This performs a search on the ingredient in column A and provides the value in 8th column right of the cell where the value has been found)
Using this formula will eliminate your question as long as
- you have not more than 1000 rows in Ingredients file (if, you need to adapt
A$5:H$1000toA5:H<max_number_of_rows> - your ingredients in columns
AofSheet 1in file Ingredients are unique. You may have “Potato 1” and “Potato 2” but you must not have “Potato” in two different rows (only the first on will be found in the latter case).
[Update] Please watch the change to A$5:H$1000 (missed the $ in the first version of this answer)
Regarding your question: To the best of my knowledge there is no way to not break links to cells/ranges which are directly linked, if you change the structure of the source file. Searching for the information you are looking for is the only way to make it somewhat independent of the structure of the source.
Hope that helps.
I’m going to try this. Thanks a lot for your reply!
Please note: This solution won’t fix a problem, if you insert columns in file Ingredients into column range A through H.
This works 100%. Is there way a way I can mark this as a solution?
Is there way a way I can mark this as a solution?
Click the check mark (
) next to the answer.