Hello all. This is my first post in the excel groups, so I was not sure
which group would be most appropriate for my question (so please forgive
the crosspost).

I want to do something that seems so simple, but I have not yet figured
out how to best do it.

I will be entering several fields of data such that a1 - b1 = c1 with
subsequent rows entered as data becomes available such that the next row
would be a2 - b2 = c2, and so on. I want a total of column 'C' such that
every day when I enter a row of new data that the total of all entries in
column 'C' moves down as new rows are added but still keeps the correct
column total.

I have thought to make this happen by writing a macro that gives the user
a data entry form, looks for the last row of data and inserts the new
data, and then moves the column total formula down one row as needed.
But I can't help but think there is a more elegant/simple way to do this
(and my VBA skills are really rusty).

TIA for your help and advice on this matter.

James

Hi "Mr. Bond",

For example (if I understand you correctly):

C1 = A1-B1
C2 = A2-B2
'''''
'''''
Cx (where x stands for the number of the cell which represents the total) =
sum(C1:Cx-1)

Ok, well I have a piece of code, which adds a new row

Sub CalcTot()

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Sheet1.Range("C1").Formula = Sheet1.Range("C2").Formula
sheet1.range(cell that calculates total).formula = "=sum(C1:Cx-1)
Sheet1.Range("cell that calculates total + 1").ClearContents

end sub

It selects Row 1, adds a new row (above row 1 which so becomes the new row
1), and copies the formula from C2. You can now simply add values on top of
the sheet, and you're done. It puts your total formula in the cell where you
want, deletes the values below it because there stood the old formula.

I think this should help it, but if you have any questions, ask.

Regards
max potters





"James Bond" wrote in message
...
Hello all. This is my first post in the excel groups, so I was not sure
which group would be most appropriate for my question (so please forgive
the crosspost).

I want to do something that seems so simple, but I have not yet figured
out how to best do it.

I will be entering several fields of data such that a1 - b1 = c1 with
subsequent rows entered as data becomes available such that the next row
would be a2 - b2 = c2, and so on. I want a total of column 'C' such that
every day when I enter a row of new data that the total of all entries in
column 'C' moves down as new rows are added but still keeps the correct
column total.

I have thought to make this happen by writing a macro that gives the user
a data entry form, looks for the last row of data and inserts the new
data, and then moves the column total formula down one row as needed.
But I can't help but think there is a more elegant/simple way to do this
(and my VBA skills are really rusty).

TIA for your help and advice on this matter.

James

=sum($c$1:c1)
copy this formula down, it is anchored at the top, but is
relative to the row you are on.

-----Original Message-----
Hello all. This is my first post in the excel groups, so
I was not sure
which group would be most appropriate for my question
(so please forgive
the crosspost).

I want to do something that seems so simple, but I have
not yet figured
out how to best do it.

I will be entering several fields of data such that a1 -
b1 = c1 with
subsequent rows entered as data becomes available such
that the next row
would be a2 - b2 = c2, and so on. I want a total of
column 'C' such that
every day when I enter a row of new data that the total
of all entries in
column 'C' moves down as new rows are added but still
keeps the correct
column total.

I have thought to make this happen by writing a macro
that gives the user
a data entry form, looks for the last row of data and
inserts the new
data, and then moves the column total formula down one
row as needed.
But I can't help but think there is a more
elegant/simple way to do this
(and my VBA skills are really rusty).

TIA for your help and advice on this matter.

James

.