Move source without altering formula

[Guest]

Here is a strange thing that somebody asked me and I couldn't find an answer.

We have a sheet where there are a lot of formulas and they are in locked
protected cells. The user fills in other unprotected cells.

Sometimes the user puts in a value and then realizes that it is in the wrong
place so they cut and past it into the correct place. When they do this it
alters the formula that was using the cell.

Example:

b2=if(upper(a2)="Y","Yes","No")
user puts "Y" into A2 then cuts it and pastes into Sheet2.A1
b2 becomes:
b2=if(upper(sheet2!a1)="Y","Yes","No")

Is there anyway to keep B2 from being changed?

 

[T. Valko]

Try one of these:

This formula is robust against row/column insertions. This formula will
*always* refer to cell A2.

=IF(UPPER(INDIRECT("A2"))="Y","Yes","No")

This formula is not robust against row/column insertions but will still
refer to cell A2 if A2 is cut and pasted elsewhere.

=IF(UPPER(OFFSET(B2,,-1))="Y","Yes","No")

Biff

 

[Dave Peterson]

Just a note:

=if(upper(a2)="y", ...
and
=if(upper(a2)="Y", ...

will both evaluate the same.

If don't care about case, you can use:
=if(a2="y",...

If you really do care about case, check =exact() in excel's help.

(This is not a replacement for Biff's answer. Just an addition.)