Formula in Cell changes even if it's locked

[Guest]

Hello,
I've created a spreadsheet to compare numbers with a "IF" statement:

=IF(LEN(D2)<1,"Blank",IF(EXACT(D2,$C$1),"Go","No Go"))

I've also locked the cells that contain the IF formula. Here's the
premise... I enter a number in one cell --> C1 and then I have a row D1 thru
D50 to input other numbers. If a number in row D matches with what's in C1,
a "Go" appears in row E (where the IF formula resides). If it doesn't match,
a "No Go" appears.

Issue: Since row D is not locked (so I can enter numbers) I'm able to
highlight it. If i highlight and drag it to a different cell, the formula in
row E somehow changes (even though it's locked)

For example:
I click and drag cell D1 to D3 and the IF formula (which is locked) in E3
changed...to the following:

Here's how it changed:
=IF(LEN(#REF!)<1,"Blank",IF(EXACT(#REF!,$C$1),"Go","No Go"))

how is that possible?

I even tried adding & within the cells and that didn't work...

Thanks in advance.

 

[Mark Lincoln]

By dragging cell D1 to D3, you're replacing D3. The original reference
to D3 in the formula in E3 cannot be resolved, as you've replaced it
with another cell. You will also notice that the formula in E1 would
refer to cell D3, as that is where you have moved cell D1. "#REF!"
means "reference error."

If you want to drag the contents of one cell to another without
triggering the error, hold down the Control key (CTRL) as you drag.
This makes a copy of the cell's contents rather than moving the cell.
You can also Copy the cell and Paste into the cell you want to copy the
text into.

 

[Guest]

thanks for the quick response. I now understand; however, is there any way
to prevent the formula in E from changing? Password protecting/locking
appears not to work.

 

[Mark Lincoln]

The formula doesn't actually change. Rather, the formula cannot be
resolved when you replace one of your cells with another, hence the
#REF! notations where you expect cells. You have to keep from moving
the cells in column D from one place to another. As I wrote before, if
you want to copy text from one cell in the range to another by dragging
and dropping, hold down the Control key as you move the cell; this
creates a copy of the contents. Otherwise, Copy from the source cell
and Paste into the target cell.

Note again that when you move, say, D1 to D3, the formula in E1 then
refers to D3 instead of D1. So D1 could be blank but E1 could read
"Go" or "No Go" depending on the contents of D3. (And of course, E3
will show #REF! because the cell its formula referred to was replaced.)

If you move a cell in column D and see a #REF! error, Undo immediately
to restore column D to its previous state. This will restore the
references and eliminate the error. Then copy as described above.