I have a Spreadsheets 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 this 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? Jan 23 '13 at 20:42
  • 1
    @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? Mar 6 '16 at 7:03

Make sure to reference columns and not ranges:

Column reference:

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

Explicit reference:


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


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.