How to embed "reset" in formula.

[Eloise T]

I receive TV model numbers with serial numbers from salesmen on Excel. Each has it's own column; Column C is the TV Model number and Column D is the Serial Number. Column A is the Invoice #. Occasionally the salesmen get in a hurry and put down the same number for both TV Model number and Serial Number, so I wrote a small formula in Column N (out of the way but still visible) to quickly detect that error:
=IF(A2<>"",IF(TRIM(C2)=TRIM(D2),"√ → TV Model/SN# match error","")," --")

The formula displays "--" if no data (invoice number) found in Column A,
"" if adjacent TV Model and Serial Number do not match (a good thing),
and "√ → TV Model/SN# match error" if there is a match indicating an error.
(obviously TV Model # and Serial Numbers can't be the same.)

THE QUESTION: When I tested my formula by purposefully putting in the same number in both Column C and Column D I got ""√ → TV Model/SN# match error" like I should. I then changed the number in Column C to be different from Column D. The first time I got: #REF! To fix that, I copied the cell formula from the cell above over the #REF! cell and it was blank again...like it should be. Everytime since I've retested the formula, it has worked "correctly" and has not replicated the #REF! again. What might have happened to cause the #REF! first time?

 

[Hui]

Do you have any VBA code in the file that is deleting rows or data?
If you copy that formula left it will try and make A2 into Column 0, 2 which will force a #REF! error

otherwise no idea?

 

[Eloise T]

Do you have any VBA code in the file that is deleting rows or data?
If you copy that formula left it will try and make A2 into Column 0, 2 which will force a #REF! error

otherwise no idea?

No VBA deleting rows or data.
Your copying idea has given me some ideas to try.