Copy this into B8 and fill across to D8:

=INDEX(B$4:INDIRECT("R[-1]C",0),COUNTA(A:A)-2)-B$4

HTH
Jason
Atlanta, GA

-----Original Message-----
I'm keeping a spreadsheet of my usage of electicity, gas
and water by taking
meter readings every week or so. The table gradually
acquires more rows over
time, as I take successive reading.

I display the total usage to date as a series of values
at the bottom of
each column:

1 A B C D
3 Date Elec Gas Wat
4 1 Jan 05 123 345 34
5 1 Feb 05 127 350 35
6 15 Feb 05 138 355 39
7
8 Totals 15 10 9


Where B8 is B6-B4, C8 is C6-C4 etc.


Each time I add a new row (the next one would be below
row 6) I need to
modify the formulae: for example B9 (which was B8 until
I inserted the row)
needs to have its formula changed from B6-B4 to B7-B4 to
include the row
I've just added. This is a tedious process.

I can label a cell but then I'd like to be able to use
the row of that
labelled cell as a limit in a formula? I'd like to be
able to label B8 as
"electricity_total" so I can set its formula to
"(col(electricity_total),row(electricity_total )-2)-B4"
such that the
row/column of the lowest cell are calculated rather than
being literals.
This way, every time I insert a row, I always include
all rows up to the
n-2'th row where n is the row of the cell that contains
the formula.


Similarly, it would be nice if I could do the same thing
in the Source Data
fields of an X-Y graph (X=date, Y=daily usage) so I
don't need to make
corresponding changes as I add more rows (eg
changing "=$A$7:$A$100" to
"=$A$7:$A$101"). Given that I'm plotting three lines
(electicity, gas,
water) and each has two values (X and Y) that's 6 ranges
that I need to
change every time I add a new row.


I'm sure this must be possible, but I have the usual
problem with online
help: trying to find a suitable phrase to search for in
the index!

Defining the name for the cell is the easy bit (Insert |
Names | Define) -
it's using that name in row() function and referring to
a cell whose row
and column are calculated not literals that has got me
baffled.






.