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

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?

-
can you give us some feedback whether the answers give, are appropriate? – Jacob Jan Tuinstra Jan 23 '13 at 20:42
@JacobJanTuinstra, with the complexity of the sheet I was attempting, none of the solutions posted were viable. I ended up giving up and simplifying the sheet. – Dan Jan 23 '13 at 21:01
Is there a way to insert a row, delete a row, or sort rows while updating references, so that they remain valid? – Vaddadi Kartick Mar 6 at 7:03

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.

-

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

-

Create a Named Range in the first sheet. Then refer to those cells in the other sheet by the named range.

The named range will refer to those cells even if you insert/delete other rows or columns before it, thus changing their cell numbers.The named range will expand if you insert rows or columns within that range.

-