really, really absolute cell reference?

[Rick]

Hi Mike,

try putting a dollar sign ($) in front of the "A" and
again in front of the "14" so that it looks like this...
$A$14

 

[mikelee]

that's the problem. absolute cell references move when
you insert or delete cells.

to better show what i'm trying to do, open a spreadsheet
and enter the formula in cell B1 =$A$14.
then right click cell A2 and choose "Insert." from the
dialog box, choose "shift cells down."

now click back in cell B1 and you'll notice that the
formula has changed to =$A$15. That's what I'm trying to
avoid. I'm trying to reference an address, not a
datapoint. I want the formula to remain =$A$14, even
after cells have been inserted or deleted which have
moved the original datapoint.

hope that makes sense.

thanks,
mike

 

[Harlan Grove]

that's the problem. absolute cell references move when
you insert or delete cells.

...

=INDIRECT("A1") will *always* refer to cell A1 in the same worksheet as the cell
containing this formula no matter what's inserted, deleted or moved.