reference to previous cell (always)

H

Harvey

This seems to be a very simple thing.
I have a list of numbers and the next colum over is a running total.
If I delete a line (or insert, etc) the references become incorrect.
I always want the formula to reference the previous cell (above) in the
running total column.
Thanks
Harvey
 
R

RagDyer

Try this:

=SUM($A$1:A1)

And copy down as needed.

OK for deletes.
If you insert, you must of course copy the formula into the new rows.

Also, wouldn't advise using this formula if you're totaling more then 3 or 4
thousand rows.

It's hard on the system, but for small ranges it's OK.
 
J

joeu2004

Harvey said:
This seems to be a very simple thing.
I have a list of numbers and the next colum over is a running total.
If I delete a line (or insert, etc) the references become incorrect.
I always want the formula to reference the previous cell (above) in the
running total column.
Click to expand...

I wonder if this meets your needs:

offset(B5,-1,-1)

assuming that appears in B5 itself. That references the cell in the
row above and the column to the left. If you copy the formula, B5
should be updated appropriately.
 
H

Harvey

offset(B5,-1,-1)

assuming that appears in B5 itself. That references the cell in the
row above and the column to the left. If you copy the formula, B5
should be updated appropriately.
Click to expand...

Good! Thanks.
That got me to this

=B5+OFFSET(C5,-1,0)

Which works great.
Now I can delete or move a row with no problem.

Harvey
 
H

Harvey

Try this:

=SUM($A$1:A1)

And copy down as needed.

OK for deletes.
If you insert, you must of course copy the formula into the new rows.

Also, wouldn't advise using this formula if you're totaling more then 3 or 4
thousand rows.

It's hard on the system, but for small ranges it's OK.
Click to expand...

Thanks for the reply, however I don't want to resum the entire column
at each line, only add the cell to the left to the one above at each
row. I got the answer from joeu2... and now

=B5+OFFSET(C5,-1,0)

does what I want.
Thanks,
Harvey
 
Top