This is a nasty bug in Excel 2007. And Microsoft probably won’t fix it in Excel 2010.
To see how this bug works, first download the BugReport and BugData workbooks and save them in one folder.
Now open BugData.xlsx in Excel and then open BugReport.xlsx. When you press the F9 key, the BugReport workbook shows the Foo Total of 1000. So far, so good.
Close both workbooks. Now open BugReport.xlsx and then open BugData.xlsx. If your calculation mode is set to automatic, you’ll see the Foo Total equal to #N/A. If the value still is 1000, press the F9 key to recalculate your workspace to see the #N/A result.
This seems to be the logic of the bug:
When Excel range names in a report workbook reference sheet-scoped names in a database workbook, if the report workbook is opened first and the database workbook is opened second, Excel deletes all sheet-scoped names that the report workbook references in the database workbook.
So here are a variety of ways to manage this problem:
1. Stay with Excel 2003, which doesn’t have this bug.
2. In Excel 2007, open all database workbooks before you open report workbooks. If you forget this step and experience this problem, close both workbooks WITHOUT SAVING. Then open the database workbook first.
3. Don’t use sheet-scoped names in database workbooks.
You might be wondering why these ranges names are used in the first place. If so, take a look at Set Up Range Names to Connect Reports to Excel Databases, Part 1, where I explain why these names are so valuable.
{ 3 comments }
You’re always right on, Charley; thank you.
I tested this with two other workbooks. I did not encountered an error.
I have the workbook calculation settings as -
1. Update Links to other documents
2. Save External Link values
Regards,
Charu
Charu,
Versions of Excel prior to 2007 don’t appear to have this problem. Also, I heard from a visitor who couldn’t create the problem from scratch. His sample workbooks didn’t use the range names in the report workbook as I describe in Set Up Range Names to Connect Reports to Excel Databases, Part 2.
Charley
Comments on this entry are closed.