Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bizarre Cut and Paste Bug (2003 sp2)

    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,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    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.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    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.
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    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.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    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.
    Regards,
    Rudi

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    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

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    Nope, haven't seen that one before. But then again, I never use data tables.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Lounger
    Join Date
    Jul 2006
    Location
    New York City, New York, USA
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    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,

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    Thanks for letting us know. I bet this bug is quite low on their list of priorities.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    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.

  11. #11
    New Lounger
    Join Date
    Oct 2002
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bizarre Cut and Paste Bug (2003 sp2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •