Results 1 to 10 of 10
  1. #1
    New Lounger gggoodwin's Avatar
    Join Date
    Apr 2012
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Excel 2010 array formula defined name bug?

    I've run into a difference in behavior between Excel 2010 and Excel 2003. It involves worksheets containing references to worksheet-level defined names in array formulas, and occurs when such worksheets are either copied or renamed. Whereas in Excel 2003, all the 'sheet name'! strings are automatically adjusted to the new (or renamed) sheet name, in Excel 2010, they retain the old sheet name! Neither A1-style references nor references in non-array formulas exhibit the problem. So far I can verify this behavior in both Office Professional and Office Professional Plus SP1. Am I missing something, or is this a bug?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    We spotted that the other day after creating a sheet in 2003 and then using and saving it in 2010. reverting to 2003 format seemed to fix it.

    cheers, Paul

  3. The Following User Says Thank You to Paul T For This Useful Post:

    gggoodwin (2012-04-16)

  4. #3
    New Lounger gggoodwin's Avatar
    Join Date
    Apr 2012
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanx Paul T! You've given me another avenue to pursue! Will be back with more after I get some time to play with compatbility mode around this problem...
    Best regards,
    ggg

  5. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    989
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I've encountered something similar in Excel 2003, when some worksheet-level names worked in an array formula and some didn't.

    It worked if I used simple names - all alphabetic characters.

    Maybe you can experiment with the names you are using ?

  6. The Following User Says Thank You to MartinM For This Useful Post:

    gggoodwin (2012-04-16)

  7. #5
    New Lounger gggoodwin's Avatar
    Join Date
    Apr 2012
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanx for that info Martin - that's something I've never tried. However, this did and does work in 2003. None of the names are new or changed from that implementation.

    Paul and Martin, here's where I am:

    • I've been using a 2003 template to do this work for several months. the template contains a "skeleton" worksheet that gets copied to make a worksheet that pulls information from several (usually more than 100) workbooks based on the name of the worksheet created from the skeleton.
    • I thought I'd move up to 2010 to see what, if any, differenecs surfaced.
    • The move involved using Save As to convert the template to .xltm
    • It was at this point I encountered some Name errors that, after some thrashing, I discovered resulted from the failure to set the worksheet context of my worksheet-level defined names to when copying and renaming the worksheet made fom the skeleton.
    • I tried using the Convert method instead of Save As, but the result is the same.

    Since my last I've determined that copying and pasting from the skeleton to the to the target worksheet does not help - in this case, no defined names convert to worksheet-level on the target worksheet, array formulas or not.

    I can always go back to 2003, but I was hoping to leverage some of 2010's more robust conditional formatting in my resutls

    Unless someone has a better suggestion, I plan to try starting with an empty .xltm, importing my code modules, and then re-entering my array formulas by copying the equation 'text' into new arrays. It's either that or dig into the XML ;D

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    I first raised the issue of missing Excel range names in this forum on 13-Jan-2009.
    You could probably search for this.

    My last response was this, on 02-Jul-2009:
    Hi All

    If you have been following the history of the "lost range names" issue in Excel 2007 you may be interested in the following:

    http://support.microsoft.com/kb/968863/en-us

    Article ID: 968863 - Last Review: June 10, 2009 - Revision: 3.0
    Description of the Excel 2007 hotfix package (Excel.msp, Xlconv.msp, Graph.msp): April 30, 2009
    Hotfix Download Available

    SUMMARY
    This article describes the Microsoft Office Excel 2007 issues that are fixed in the hotfix package that is dated April 30, 2009.

    You have an Excel workbook that contains defined names. The names may be silently removed during save.

    '--------------
    Well, thank you Microsoft.
    That explains a lot.

    The hotfix is 18.9MB download.
    But you need to have SP2 first. That's a 290MB download.

    I thought I'd share this with you.

    zeddy

  9. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 985 Times in 915 Posts
    We too use a template with range names. The names are not removed, the worksheet paths are screwed so the calculations don't work.
    I'll try to test it tomorrow and report exactly what happens.

    cheers, Paul

  10. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It looks like a bug to me, since it is inconsistent with non-array formula references. If you omit the sheet name, it works as expected. I will file it as a bug later.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. The Following User Says Thank You to rory For This Useful Post:

    gggoodwin (2012-04-18)

  12. #9
    New Lounger gggoodwin's Avatar
    Join Date
    Apr 2012
    Posts
    4
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanx Rory!

  13. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    I have now filed it. Will let you know if I get any response that I am allowed to share.
    Regards,
    Rory

    Microsoft MVP - Excel

Tags for this Thread

Posting Permissions

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