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.