Auto selecting cells

[Monkey_Bpy]

I would like to know, if anyone knows how to do it, how to set a frormula in a cell so it always takes its information frmo the cell above it, along side it or both in a column or row.

For example: If the cell above says 100 and the cell alongside it says 20 and I would like to know what one cell less another is how do I do this without having to set up a formula which I copy and paste all the way down the spreadsheet.

This is necessary as I intend to have breaks in the columns to aid the readability of the spreadsheet (this breaks the logic of a cut and paste formula meaning I have to start the process over and over again).

[Mark@Work]

Try something like:

=INDIRECT(ADDRESS(ROW()-1,COLUMN())) - INDIRECT(ADDRESS(ROW(),COLUMN()-1))

ADDRESS creates a text representation of the cell address (eg C5) based on the row and column numbers supplied to it.
INDIRECT gets the value from a cell address which is suplied to it as text.
ROW is the current row, COLUMN is the current column, thus ROW()-1 is the row above and COLUMN()-1 is the column to the left.

Mark.

[Monkey_Bpy]

Using the below formula I am now able to reference the last populated cell in a column.

=LOOKUP(9.99999999999999E+307,D:D)

However. I need to be able to use this formula in the same column in which it is searching (i.e column D). UNfortunatly it stops working, presumably as it is trying to reference itself. Is there a way to use this forumla without it referencing itself?

Cheers,

Monkey_Bpy