Target cell reference moves when target is cut and pasted

[Illya Teideman]

In cell A1 type =B1
now in B1 type the number 10.
A1 looks at B1 and shows the value 10.
If I then cut the contents of B1 and Paste them into C1, A1 will still show
the value 10 and the formula has changed to =C1.
My question is how do I make 100% sure that the formula I put into cell A1
will always look at B1 regardless of cutting and pasting actions in the
target reference cell. Please note before answering putting $ in front of the
formula eg =$B$1 makes no difference the formula wil just become =$C$1. Thank
you for your assistance in advance.

 

[hmm]

Use copy and paste instead of cut and paste.

 

[Illya Teideman]

That does not answer my question. That is the sensible thing to do yes but I
need to ensure that whatever happens to the target cell that the formula
always points in that reference including if the data is cut and pasted. I
suspect the answer will be in the formula rather than the process used to get
the data from one cell to another as that is something I cannot control. This
is for a form that will be used by data entry.

 

[b&s]

.... In cell A1 type =INDIRECT("B1")

That does not answer my question. That is the sensible thing to do yes but
I
need to ensure that whatever happens to the target cell that the formula
always points in that reference including if the data is cut and pasted. I
suspect the answer will be in the formula rather than the process used to
get
the data from one cell to another as that is something I cannot control.
This
is for a form that will be used by data entry.

 

[hmm]

How about typing into A1 =INDIRECT("b1"). A1 will always refer to B1, even
if B1's contents are moved or, sometimes problematically, even if rows or
columns are added before B1. In your example, if a single column is added to
the left of column B, you will get a circular reference. Will that work for
you?

 

[Illya Teideman]

Excellent thank you that seems to do the trick

How about typing into A1 =INDIRECT("b1"). A1 will always refer to B1, even
if B1's contents are moved or, sometimes problematically, even if rows or
columns are added before B1. In your example, if a single column is added to
the left of column B, you will get a circular reference. Will that work for
you?