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

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.

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

• so, it's kinda like eval() ? (php) Jan 21 '16 at 20:55
• @DougCassidy: I don't think so. INDIRECT only returns a cell/range reference, it don't execute code.
– Rubén
Jan 21 '16 at 21:05

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.

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

• No, this does not do what the OP wants. If you insert a row on top, the formula will be changed to `=sum(D\$4:D)`. He wants it to start from the 3rd row, even if new rows are added/deleted/moved.
– user79865
Jan 23 '16 at 6:26
• You are right. It looks \$ only locks the value when copying formulas Jan 23 '16 at 17:42
• @user79865 is there a way to apply the formula for the inserted above row? Mar 3 at 23:22

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.