Insert Row without shifting formulas down

Question

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?

Answer 1

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"))

Answer 2

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:

Then your formula is just =SUM(StaticRange)