My situation is as follows:
Sheet1!A1:Sheet1!A6 contain six numeric values, say 1 through 6, respectively.
Sheet2!A1:Sheet2!A3 contain three cell references to Sheet1!A1 through
When I cut cells Sheet1!A4:Sheet1!A6 and paste them over Sheet1!A1:Sheet1!A3
all the cell references on Sheet2 get replaced with "=Sheet1!#REF!".
If I perform the same operation by copying the data (rather than cutting
it), pasting, then deleting the copied data I get the expected
behavior--seeing the new data for cells Sheet1!A1:Sheet1!A3 appear by
reference on Sheet2 in the proper cells.
I also noticed that if I use a cut operation I cannot "Paste Special" but if
I use a copy operation I can.
I referenced the help on this error. From the "Correct a #REF! error" help
may have pasted cells that you moved on top of cells that were referred to by
So that tells me that I will see the behavior I am seeing, but it does not
tell me why. Interestingly, this statement would lead me to believe that if
I highlighted cells Sheet1!A1:Sheet1!A3 and pressed the delete key I should
get a #REF! error on each of cells in the range Sheet2!A1:Sheet2!A3; this,
however, is not the case.
I don't understand why cut operations are treated fundamentally different
than copy operations in Excel. Isn't cut just a copy followed by a delete of
the original data? Yet if I do a copy followed by delete I see dramatically
Thanks in advance.