Question Locked

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

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

6 replies

by databaseben

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.

by tinyhands88

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?

by john.a.wills

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.

by rodney.mcmullan

Hi,

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.

Rodney

by al kostiuk

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:A8) 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.
Al

This discussion is closed to new replies.