Excel: want a cell reference to another sheet that won't move!

[owen.vonk]

I want to make a rock solid cell reference to a cell in another sheet -
a cell reference that will not move if the data in the other sheet is
moved:

I want to tell cellF2 in sheet2, to display the info in a cellA1 in
sheet1. When I drag the data in cellA1 in sheet1 to another location
(say C1), I DO NOT want cellF2 to have its cell reference updated to
the new location (C1) of the data in sheet1.

I want cellF2 in sheet2 to lose the data (show blank or 0).

None of the following formulas does this:
=Sheet1!A1
=Sheet1!$A1
=Sheet1!$A$1

Thanks in advance,
Owen

 

[owen]

I want to make a rock solid cell reference to a cell in another sheet -
a cell reference that will not move if the data in the other sheet is
moved:

I want to tell cellF2 in sheet2, to display the info in a cellA1 in
sheet1. When I drag the data in cellA1 in sheet1 to another location
(say C1), I DO NOT want cellF2 to have its cell reference updated to
the new location (C1) of the data in sheet1.

I want cellF2 in sheet2 to lose the data (show blank or 0).

My temporary workaround is to copy the data in cellA1, hit delete to
clear the cell, then paste the data elsewhere in sheet 1:
The cellF2 loses the data and the its reference to CellA1 does not
change.... not elegant, but but it works = )

Anyone have a more elegant solution?

- Owen

 

[JE McGimpsey]

I want to tell cellF2 in sheet2, to display the info in a cellA1 in
sheet1. When I drag the data in cellA1 in sheet1 to another location
(say C1), I DO NOT want cellF2 to have its cell reference updated to
the new location (C1) of the data in sheet1.

I want cellF2 in sheet2 to lose the data (show blank or 0).

One way:

=INDIRECT("Sheet2!A1")