Keep absolute reference even when inserting rows in Excel 2007

Question

I have a spreadsheet where I want cell formula to always look at a specific cell, even if rows are inserted and the specific cell moves. Effectively, I always want to look at the 'top' cell of a table, even if new rows are inserted at the top of the table.

eg. Cell A2 has the formula[=$E$2]

Now I highlight row E and do Insert Row. The formula in A2 now says [=$F$2] but I want it to be looking at the new row E.

The dollars will keep an absolute cell reference no matter what I do to the 'referencing' cell, but I want the cell reference to be absolute nomatter what I do to the 'referenced' cell. If that makes sense!

Effectively, I have a 'table' in excel 2007 and I want to always reference the top row. The trouble is that rows are added to this table from the top so the top row keeps moving down to make room for a new top row.

--- Alistair.

Answer 1

Try =indirect("F2"). This will work if you know that the top-right cell of the table is always going to be $F$2.