dynamic expansion of formula ranges

[kurifodo]

Hi everyone!

I've come accross a slight problem for my cashflow.
In essence it seems simple but the answer has been avoiding me now for days... It's as follows:
I have a sum of the total income (say A6=Sum(A1:A5)) which can increase or decrease depending on the object evaluated.
So the problem arises when I insert a row at the end, A7 will still only calculate Sum(A1:A5) and A6 (new value) has to be added manually.
It wouldn't be a problem if it needed to change once in a while but its an almost daily activity example below:

A1 : 1
A2 : 2
A3 : 3
A4 : 4
A5 : 5
A6 : 15 (total)

Range increase:

A1 : 1
A2 : 2
A3 : 3
A4 : 4
A5 : 5
A6 : 6
A7 : 21 (total)

Any help would be much appreciated!

Kurifodo

[Pete_UK]

Insert a new blank row or two at the top of the sheet and put your total formula there (in the new A1). Then you can have the range much larger than you really need to accommodate extra entries, eg:

=SUM(A2:A2000)

Hope this helps.

Pete

[FDibbins]

could you put the new row in btw 4 and 5, instead of 5 and 6?
or leave a blank row btw 5 and 6, make your sum() incude the blank row, and insert new rows above the blank row?

[kurifodo]

Putting the new row in between did do the trick indeed! It truly is the case that the simple answer is the one escaping you!


Thanks a lot for the help / brain storm

Kurifod