Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
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
add comment

2 Answers 2

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

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

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.