Bizarre Cut and Paste Bug (2003 sp2)

Topic

New Reply

Hi. We ran into a strange problem today. If you have a sheet with one or more data tables on it, when you cut a cell from any other sheet and paste it into the data table sheet, all the data tables #REF out. Instantly, without calculation, the formulas change to =TABLE(#REF, etc., as if you just cut the cells to which they refer. It doesn’t happen if you copy and paste.
It doesn’t happen in Excel 2007. But it happened for us, our clients, and microsoft’s folks, even with newly created files. Strange.
Wondering if this is new behavior, or if it’s been around for a while. Has anyone seen this before?
Thanks,

Reply | Quote

Replies

I seldom use data tables so I hadn’t encountered this, but I can reproduce the error in Excel 2002 SP3. Very strange – I have no idea why it occurs, and I cannot find any mention of the problem in the newsgroups.

Reply | Quote

This does happen in Excel 2007 also.

What is happening is that you are pasting the cut value into one of the input cells that make up the data table. Since you are cutting the value from the source location, and pasting it, it looses its reference and causes the data table to #Ref.

It also happens if you do this in a standard formula. Try this:
– Enter 10 in cell A1 and 20 into cell A2
– In cell C1 type a formula: =A1+A2+A3 and enter to confirm 30.
– Cut a cell (with a value or blank) from another location and paste into cell A3 (or A1 or A2).
– When you ENTER, you get a #REF

This is what is happening in the data table too.

Reply | Quote

Rudi, it happens (in Excel 2002) if you cut and paste a cell anywhere in the sheet, even if the target cell has nothing to do with the data table.

Reply | Quote

Apologies for mis-understanding. I thought I had the solution there when I saw that it #Ref’ed out when I pasted into the input cells. So I should reconfirm that this is not a problem in 2007 anymore.

Reply | Quote

I can not test in XL2003 or XL2007, but I can also reproduce the problem in XL2002.

The problem does not occur in XL97, however, so it appears to be a bug starting in XL2002 and fixed in XL2007…

Steve

Reply | Quote

Nope, haven’t seen that one before. But then again, I never use data tables.

Reply | Quote

Thanks very much for all the replies.
Microsoft Premier Support investigated and have confirmed that it is a bug. Which is obvious, of course, but they’ve never gotten back to us so fast on anything before. (Sounds like there are no plans to fix it though.)
What amazes me is that, while most people avoid data tables, they are very very common in financial modeling, yet this seems never to have been noted before.
Best,

Reply | Quote

Thanks for letting us know. I bet this bug is quite low on their list of priorities.

Reply | Quote

Since the problem has been corrected in Excel 2007, I don’t think there is any chance that it will be fixed in Excel 2003 or 2002.

Reply | Quote

Actually if you open a spreadsheet in Excel 2007 that has formulas etc. and cut a range of cells then paste them elsewhere, it messes up all the related formulas. All of the cell formatting is also removed from the location where the cells were cut.
We’ve just run into this as well and I can’t find any mention of a fix except some code to disable the Cut function.

Reply | Quote