In a Google Spreadsheet, can I avoid references to cells in other sheets from breaking if I remove/add a row?

Question

I have a spreadsheet that has two sheets. One takes entries and a second manipulates content from the first and displays results.

If I remove or add a row, the references in sheet two change, killing the desired content in that same sheet. If I delete row two from sheet one, what becomes row two in sheet one does not replace the content that was in sheet two, instead the cell breaks, referencing a non-existent or irrelevant cell.

I know what's happening. Instead of referencing a position relative to the sheet, it's referencing the actual cell, but not only does that not make sense, it doesn't work for my intended purpose.

Can I change this functionality or is there a better way to approach the issue?

Answer 1

Make sure to reference columns and not ranges:

Column reference:

=SUM(A:A) or =SUM(A30:A)

Explicit reference:

=SUM(A2:A30)

At most, you need to re-populate the formula's. Walk through the spreadsheet I prepared and see the formula's for yourself: file I prepare with example

Answer 2

To keep references, I avoid to delete edge cells. Let say I have SUM(A1:A10) and I need few more lines. Then I add them between 9 and 10 rows. When deleting, I avoid to delete rows 1 and 10, others are safe to delete.

If you have reference to another sheet single cell (without formula) - then yes, it is problem.