|
Why does "cut and paste" cause #REF! errors?
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 |
Answer: Why does "cut and paste" cause #REF! errors?
Hi Mike,
The reason why "cut and paste" causes #REF! errors is because when you cut a cell or range of cells, Excel removes the original cells and their contents from the worksheet. This means that any formulas or references that were pointing to those cells will now be pointing to non-existent cells, resulting in the #REF! error. In your specific situation, when you cut cells Sheet1!A4:Sheet1!A6 and paste them over Sheet1!A1:Sheet1!A3, the cell references on Sheet2 are now pointing to cells that no longer exist. This is why you are seeing the #REF! error. When you perform a copy operation followed by a delete, Excel is able to maintain the original cell references because the original cells still exist until you delete them. This is why you are not seeing the #REF! error in this scenario. To avoid #REF! errors when cutting and pasting cells, you can try using the "Insert Cut Cells" command instead. This command will move the cells down or to the right to make room for the cut cells, rather than simply removing them from the worksheet. This will help to maintain the integrity of any formulas or references that are pointing to the cut cells.
As for why cut operations are treated differently than copy operations in Excel, it is because cut operations involve the removal of cells from the worksheet, whereas copy operations do not. This fundamental difference in the way the operations work is what causes the different behavior. Hope this helps! |
Why does "cut and paste" cause #REF! errors?
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 |
Why does "cut and paste" cause #REF! errors?
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 |
Why does "cut and paste" cause #REF! errors?
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 |
Why does "cut and paste" cause #REF! errors?
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 |
| All times are GMT +1. The time now is 05:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com