Lock the formula of a cell, even if the cells it references change

Question

I have this, in D1:

=sum(D3:D)

I want D1 to always be the sum of the rest of column D (D2 is a header)

I sometimes move a row, from eg 8 to 3, and that causes D1 to change to sum(d4:d).

I want D1 to stay as my sum of the column.

Answer 1

Short answer

Use =SUM(INDIRECT("D2:D"))

Explanation

Google Sheets will update A1 notation references when rows or columns are inserted, so if you need that a reference never changes, instead you have to use a string and the INDIRECT() function to convert it to a reference.

References

• INDIRECT - Google Docs editors Help

Answer 2

With INDIRECT, I had trouble when I inserted a new row or column and everything shifted and that's where OFFSET comes in.

Use =sum(OFFSET(D2:D,1,0))

This will make sure your formula is correctly updated when you insert cells and when you copy paste them.

Answer 3

Easier way: =sum(D$3:D). the $ locks the starting row.

Answer 4

I'm becoming acutely aware that certain functions that interpret contents are very time consuming. Indirect is one of the ones I avoid.

Another approach is to put a buffer row at the start and end of the row range. You can make the height small, and fill with a colour or hide the row.

So Row 1 is headers Row 2 is whatever you currently have as row 2. Row 3 is your buffer row. D1 = sum(D$3:D)

Now you can rearrange as you want.

If you include Row 3 in your frozen header section, you can even sort your spreadsheet. It won't affect the frozen rows.