Apologies if this post is now duplicated. I did try replyin to a previous thread but thus far no one has replied, which is unusual for these forums so I thought I would start a new thread. In short I'm losing Defined Names in Excel 2007 when I "File > Save As > Excel 97-2003 format".
I've been developing a model in Excel 2007 that makes extensive use of Defined Names to store repetitive formulas and/or as means of keeping functions in cells reasonably easy to read. I've used this approach extensively in the past without any issues, albeit in versions of Excel prior to 2007.
While I am working in Excel 2007, because of the client I am working with I need to deliver my model in Excel 1997-2003 compatible format and I think this is the root cause of these issues.
The situation I now have is that doing a File Save As (Into an Excel 1997-2003 format file) will now routinely remove the same 4 Defined Names. i.e. They are there but have are set to "=#VALUE!". The problem does not occur if I File Save As into a 2007 format version of the file.
Other observations. The 1997-2003 format file is about 2Mb, the 2007 version is considerably smaller (as you would expect) at 271Kb. OK. The working 1997-2003 format of the file is 2,313 KB. The crocked version of this file, created via File Save As, is 2,218 KB. clearly there is something "missing" from the crocked version(s).
I've ported the working 1997-2003 format version (2,313Kb) over to another PC running Office 2003, opened the file without issue. All Defined Names are present and correct. However, on this PC this file shows as being 951Kb in size. i.e. Different to what is reported on Excel 2007? N.B. Just done a test on the Office 2003 based PC by opening an archived copy of a previous version of the Excel 2007 created file (in 97-2003 format) and done a File Save As. The file changes from circa 1.9Mb to 704Kb when saving via Excel 2003 rather than Excel 2007. Is any of this of significance?
Also tested porting an Excel 2007 version of the file to the Excel 2003 based PC. I have the necessary Microsoft conversion software to open Excel 2007 files in Excel 2003. The same four Defined Names are missing when I open this Excel 2007 file in Excel 2003. However, the Defined Names are there and working if I open the original file in Excel 2007.
I'm currently working on an Office 2003 based PC without any of the above issues, but it's not really a tenable long term solution.
Any of the above help, shed any light? Oh yes.. I'm fully patched and I've downloaded the hotfix mentioned earlier in this thread.