CODEWITHSUNDEEP
excel
user1296932
user1296932

Reputation:

Insert Row without shifting formulas down

I want to insert row for each day to add new values, and I have a SUM formula to add up all the values from each day.

How do I stop excel from automatically shifting my SUM formula's range down when I insert a row?

Upvotes: 4

Views: 15056

Answers (3)

Dominic P
Dominic P

Reputation: 2380

Old question, but I just ran into this today. In my case, I was able to avoid the issue by using OFFSET() to define the range for the SUM() instead of entering the range directly.

So, instead of =SUM(A2:A50) you can use =SUM(OFFSET(A1, 1, 0, 50)). How does this help? If the cell that you reference in the OFFSET() function is outside the range for the formula, Excel typically won't modify it when you insert/delete rows.

Your mileage may vary since the inner workings of Excel are a bit mysterious to me, but this technique worked well for me once I understood what the OFFSET() function was doing.

Reference: http://dmcritchie.mvps.org/excel/offset.htm

Upvotes: -1

Doug Glancy
Doug Glancy

Reputation: 27488

I'd use a named range with the formula:

=INDEX(Sheet1!$H:$H,4):INDEX(Sheet1!$H:$H,50000)

To create it do Ctrl-F3 and fill in the name and definition. In Excel 2010 it would look like this:

enter image description here

Then your formula is just =SUM(StaticRange)

Upvotes: 4

Isaac Fife
Isaac Fife

Reputation: 1690

put a dollar sign in front of what you don't want to change. If the range is A1:B2, make it $A$1:$B$2. Add and remove each dollar sign as needed.

EDIT: use SUM(INDIRECT("$H$4:$H$50000"))

Upvotes: 10

Related Questions