help microsoft excel?

Question

In using absolute cell references, how do you keep absolute reference if you insert/delete a row or column? here's the situation:

i have an absolute reference to a cell on another worksheet, which i continually add data to by inserting a new row at the top of the sheet, where i want to have my most recent data. from a different worksheet, i use an absolute reference to that first cell (the one where i just inserted the most recent data). however, upon inserting a new row, the refernce to that cell now points to the cell that got bumped down from inserting the new row. i want to have an absolute pointer to that first cell at all times, no matter how many rows i insert.

does anyone know how to do that?

Answer 1

Did you try defining the cell?
Go to insert menu and highlight the "name" then click the "define". Input a name to the cell reference.
Use the cell "name" in the formula. In that way it would not be affected even you insert rows or columns before the cell reference in the formula.

Hope it helps.

Answer 2

I think you are only using part of the absolute reference? Try this:
$A$1, I think that should hold it.

What this does is hold the column position: $A
Then to hold the row position: $1

Just to be safe:
=SUM(Sheet1!$B$5)
Anything more I can not help then.

Answer 3

Go to www.mrexcel.com

Hint: $A1*$B1 is an absolute reference, if memory serves. Include the sheet reference to the example and you're good to go.

Answer 4

Use the indirect function

eg =INDIRECT("Sheet1!$B$12")