Awesome! Thank you Jim and Jon for your help here. This makes a lot more
sense. I never considered that some operations may operate on the cell
itself and others may operate on the contents of the cell.

May I suggest that Excel need some better documentation in this area?

Thanks agian!

mike




"Jon Peltier" wrote:

Cut - Cut deletes the cell and puts it in a new cell it it's spot. The
cell
is phisically deleted and replaced.

Actually, the cell which is deleted is the cell that the cut cell is pasted
onto. Cut and Paste is like dragging and dropping the cell.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Jim Thomlinson" wrote in message
...
There are 3 things that we are talking about here. Cut, Copy, Delete and
Clear Contents. One thing to note about a cell in XL is that you can think
of
it as an actual physical thing. Based on that we can go through each of
the
options...

Delete - You are physically deleting the cell when you do this you will be
asked how you want to shift the cells around the cell you are deleting
becuase it will be completely gone. A new cell will be created at the very
bottom or on the very right of the cells being deleted.

Clear Contents - When you hit the delete key you are actually just
clearing
the contents of the cell. Note that when you do this all of the formatting
stays. The only change being made is that the formula or value is being
erased. The actual physical cell is still there, it is just empty.

Cut - Cut deletes the cell and puts it in a new cell it it's spot. The
cell
is phisically deleted and replaced.

Copy - Copy makes an exact copy of the cell that you can put somewhere
else.
The cell is not being deleted.

Any time a cell is deleted any formulas that referenced that cell now
reference nothing so you will get a #ref error. Cut, Delete (and Move) all
perform a delete so you are prone to getting #ref errors. Clear contents
and
copy do not delete the cell so there are no worries here...
--
HTH...

Jim Thomlinson


"miket" wrote:

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