How can I link Excel cells when I am constanly adding and removing cells?

By tinyhands88 ·
I am attempting to cost out food products sold by our culinary program and have tried to link cells from one workbook to another but encounter problems when I add or remove products from my inventory lists. When the inventory cell is shifted up or down it changes the price on my recipe workbooks which has required me to go back and manually change all the formulas to link the new cells. I'm wondering if there is an easier way to do this? I would like to be able to add and subtract new products without having to go back to my 10 or so recipe workbooks and change every single ingredient price.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

try naming a range of cells

by databaseben In reply to How can I link Excel cell ...

try using the methodology of using "range" and "names".

that way you can add and delete items in a named range but the formula referencing the name ranged will stay constant.

Collapse -

Reponse To Answer

by tinyhands88 In reply to try naming a range of cel ...

To be honest, I'm not well versed in Excel so I'm not sure how to go about doing this. I've looked around online but haven't yet found any instructions that make sense. Can you explain it more please?

Collapse -


by john.a.wills In reply to How can I link Excel cell ...

will sum everything in column F from row 1 to row 8. I think that's what you want to know, or at least part of it.

Collapse -

Don't remove recipe items

by rodney.mcmullan In reply to How can I link Excel cell ...


simply leave your recipe items, don't delete them.
Have an index column in your spreadsheet runing from 1,2,3 etc... and add new recipe items at the bottom.

If I had sight of your workbook I could better advise but don't delete or move anything on this first sheet would be my recommendation.


Collapse -

Add or delete entire rows

by Al Kostiuk In reply to How can I link Excel cell ...

If you select the entire row to delete the linked formulas will automatically adjust to total just the remaining cells. If you insert a row with a list of values the same will apply. So if your total was sum(A1:A and you insert a new row at row 7, the formula will extend to include the new row. If you add it below row 8 the formula does not automatically extend the sum range.

Related Discussions

Related Forums