Total or Sum Cell Location Always Just Below Latest Row Entry. Possible?

[frank1492]

I would like to have the location of the cell bearing the total of a
column of figures vary with the number of figures in that total.
EXAMPLE: I place an entry in row 1. The total appears just below
it in row 2. I add an entry in row 2. The total of row 1 and 2 then
moves to the cell just below the row 2 entry (row 3), and so on. If
the total has a label to its left, I would like that label to move as
the total moves.
I'm sure this is possible, I just can't figure a way to do it!
Help much appreciated! Thanks in advance!
Frank

 

[Trevor Shuttleworth]

For column A:

in cell A2, put the formula:

=SUM(A$1:OFFSET(A2,-1,,,))

You can now add rows before the total row and they will be included in the
total

Regards

Trevor

 

[Roger Govier]

Hi Frank

One way
=SUM($A$1:INDEX(A:A,MATCH(99^99,A:A)))

 

[Harlan Grove]

I would like to have the location of the cell bearing the total of a
column of figures vary with the number of figures in that total.
EXAMPLE: I place an entry in row 1. The total appears just below
it in row 2. I add an entry in row 2. The total of row 1 and 2 then
moves to the cell just below the row 2 entry (row 3), and so on. If
the total has a label to its left, I would like that label to move as
the total moves.

....

So you start out with a blank worksheet, enter 1 in cell A1, and you
want the total, namely, 1, to appear in A2 and something like the
label Total to appear in cell B2. Note that the total of a single
entry is the single entry itself. Then if you enter 2 in cell A2 you
want the total of column A, 3 now, to appear in cell A3 and the label
Total to appear in cell B3 and cell B2 to appear blank. Then you enter
3 in cell A3, and you want the total, 6 now, to appear in cell A4 and
the label Total to move from cell B3 to cell B4.

If this is what you mean, you'd need to use a Change event handler
macro or a user-defined function to indicate whether a given cell
contains a formula or not. Are you willing to use VBA?

 

[frank1492]

Thanks to you both. I knew it wasn't simple!

 

[KC Rippstein]

If you're using Excel 2003, try converting your data to an Excel List.
Select all your data with headers and go to Data->List->Create List. Now
with that same data selected, go to Data->Toggle Total Row.
The built-in Excel List feature specifically sets a place for you to add new
records to your existing list (represented by a blank row with a blue
asterisk) and the totals row moves down the page accordingly, just as you
have asked.
This was a new and very useful feature of 2003.

- KC