M

#### Martin Underwood

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.