Dynamically sum a column

Select Cell A1 and use the Name Manager to create a Named Range: ptrFirstCell =

Formula:

=Sheet1!A$1

Now, select A2 and use the Name Manager to create a Named Range: ptrPrevCell =

Formula:

=Sheet1!A1

You can now use the formula in any cell, except row 1 (in any column) and it will sum all the cells from row 1 to the row before the formula:

Formula:

=IF(SUM(ptrFirstCell:ptrPrevCell)=0,"",SUM(ptrFirstCell:ptrPrevCell))

If you put the formula in row 1 it will generate a circular reference.

Courtesy the book: Progessional Excel Development by Buulen, Bovey and Green

You can insert rows up to, and including the cell with the formula

Regards, TMS