Excel 2010 array formula defined name bug?

[gggoodwin]

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?

[Paul T]

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

[gggoodwin]

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

[MartinM]

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 ?

[gggoodwin]

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

[zeddy]

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

[Paul T]

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

[rory]

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.

[gggoodwin]

Thanx Rory!

[rory]

I have now filed it. Will let you know if I get any response that I am allowed to share.