Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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.

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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