Hi,

This really has nothing to do with pasting between sheets, try this:

In cell A1 enter the formula =A2

Now select B1 and cut and paste it on A2. Cell A1 will return a ref error.
This is Excel's correct behavior.

Depending on what you are trying to do you could use Copy and Paste instead.
You can keep the references fixed by making them absolute before you copy,
$A$6, for example.

Cheers,
Shane Devenshire
Microsoft Excel MVP

"miket" wrote in message
...
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
Sheet1!A3, respectively.

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
page:

"You may have deleted cells that were referred to by other formulas, or
you
may have pasted cells that you moved on top of cells that were referred to
by
other formulas."

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
different behavior.

Thanks in advance.

mike