EXCEL2003 : Changing the cell references in a SUM

[silverback]

Well, this is embarrassing. All these years I thought I knew how to do basic things in Excel . . . .

I have some cells which have a SUM formula at the end e.g.SUM(A4:A10)
I want to be able to insert new rows in this set and have the SUM automatically change to reflect the insertion - so if I insert (using the Excel INSERT function) 2 new rows, I want the formula to automatically change to SUM(A4:A12).
It doesn't - it stays at SUM(A4:A10)
What am I doing wrong, please?
Silverback

[Rudi]

You might be doing nothing wrong; you just need to actually enter values into those two new rows.

So if your formula is =SUM(A4:A10) and you enter two new rows just above the formula, the formula now sits in A13 with row 11 and 12 without values. If you double click the formula, it still shows =SUM(A4:A10), but if you type value in A11 and A12, the formula auto adjusts its range to include these two values.

[HansV]

I'm not sure Excel will adjust the formula if you insert a row or rows below A10. You could do the following:
- Leave A11 blank.
- Change the formula to =SUM(A1:A11)
- When you need to expand the data, insert a row or rows in A11.
- Excel will automatically adjust the formula.

[silverback]

Rudi, but Hans wins again!

Silverback

[Rudi]

LOL... Yep, Hans is the Usain Bolt of the lounge and quite untouchable. I did (rather embarrassingly) overlook the fact that you are using Excel 2003. When you upgrade to later versions, you will find that Excel auto maintains the formulas when you insert rows just above them.

Cheers