Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have an array formula that I tried to use in a workbook with lots of sheets and all I get is a #NUM! result. If I copy and paste to a new workbook (Ctrl+C/Ctrl+V), the array formulas work. If I move a copy (edit/move/create copy), the array formulas don't work.

    My obvious workaround is to copy to a new workbook, but I'd really like to understand why it works in one instance and not in another. The workbook with the cut/paste is "826 that works.xls" and the workbook with move/copy is "826 that doesn't work.xls".

    Additional information. The original, multi-sheet workbook was created in Excel 2007 in compatibility mode and does contain unrelated pivot tables. However, if opened in Excel 2003, the different workbooks show the same response.

    My guess is that it's another PEBKAC error..........

    Thanks for the help!
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If I open the workbook that doesn't work in Excel 2002, select Tools | Options... and activate the Transition tab, the "Transition formula evaluation" check box is ticked. When I clear this check box, the errors disappear.
    In the workbook that works, the check box is already clear.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='799431' date='22-Oct-2009 22:10']If I open the workbook that doesn't work in Excel 2002, select Tools | Options... and activate the Transition tab, the "Transition formula evaluation" check box is ticked. When I clear this check box, the errors disappear.
    In the workbook that works, the check box is already clear.[/quote]

    I've seen transition formula tick also effect string concatenation as well.
    Andrew

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Unless you have really old Lotus 1-2-3 spreadsheets there is no reason to use any of the transition options.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='799450' date='23-Oct-2009 00:58']Unless you have really old Lotus 1-2-3 spreadsheets there is no reason to use any of the transition options.[/quote]

    Very True, but it is amazing what people click on accidentally.
    Andrew

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    gentlemen:

    Thanks for the continuing education......

Posting Permissions

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