At 20:27 13/04/2010 +0200, Michelle Konzack wrote:
Now I have only to solve the sum(over) art which can only make the
SUM of a range of ROWS (eg C5 to C999) which does not work if I
insert new ROWS because the C999 is not updated to C1000.
In fact, the formula will be updated correctly if you add rows
anywhere except at the top or bottom of the row range. But adding
such new rows at the bottom is an obvious and common need, of course.
Here's a workaround. If your total value for C5:C999 is in C1000, instead of
=SUM(C5:C999)
use
=SUM(C5:INDIRECT("C"&ROW()-1))
ROW() is the row number of the cell in which the total is to appear -
here 1000. Subtracting one from this and concatenating it with the
column label "C" gives the required cell name "C999". The INDIRECT()
function converts this string to an actual cell reference. If you
insert a new row 1000, the total cell will move down to row 1001 and
the formula will now total the range C5:C1000.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
