In Excel Bug Deletes Some External Range Names Automatically I discussed a bug that exists in Excel 2007 and in the beta of Excel 2010. Here’s the summary of that entry:
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.
This advice doesn’t mean much if you don’t use sheet-scoped names. So let’s talk about the scope of range names by first talking about print ranges…
When you assign a print area, you’re actually assigning the range name “Print_Area” to your worksheet. Then, when you print your worksheet, Excel prints only the Print_Area range if it’s been defined.
This approach worked well in the early days of Excel when workbooks could have only one worksheet. But then Microsoft introduced multi-sheet workbooks with Excel 5 in 1993. The story goes that Microsoft was demonstrating an early version of Excel 5 to a group of developers. One of them asked, “We now can have several sheets in a workbook. But we can have only one Print_Area range name. So how can we assign a print area to each sheet in the workbook?”
The programmers huddled on the stage, the story goes, and finally told the developer, “We’ll get back to you about that.”
What the programmers came up with was a way to scope a range name. That is, Excel range names can have a workbook scope or a worksheet scope. To see how they work, suppose you define “Foo” to refer to some range of Sheet 1 of a new workbook. By default, this will be a workbook-scoped name. Now, in Sheet 2, suppose you define an area of the worksheet as “Sheet2!Foo”. Doing so defines that range as a sheet-scoped name. (The New Name dialog in New Excel provides a drop-down list box that allows you to specify the scope more easily than in prior versions of Excel.)
Now let’s see how these names work…
- Activate Sheet 2. Press the F5 key, enter “Foo” as the Reference in the GoTo dialog, and then press OK. Excel will select the sheet-scoped name for Sheet 2.
- Activate Sheet 3. Use the GoTo dialog again to go to “Foo”. This time,Excel will select the range defined by the workbook-scoped name, which refers to Sheet 1.
- Activate Sheet 3. Use the GoTo dialog to go to “Sheet2!Foo”. This time, Excel will select the range name defined by the sheet-scoped name, which refers to Sheet 2.
You can have one workbook-scoped names in a workbook, but have as many sheet-scoped names as you have worksheets.
And that brings us to the database workbook…
Until now, I’ve recommended using multiple worksheets in database workbooks. For example, you could have one database in a sheet named Actual and another database in a sheet named Target. Both of those databases could be assigned the sheet-scoped name Data. With this approach, you could reference the two ranges with a references like this:
=[Database.xls]Actual!Data
=[Database.xls]Target!Data
That worked very nicely until Excel 2007. But as I explained in my blog entry about this Excel bug, with Excel 2007 (and probably with Excel 2010), you must open Database.xls (or Database.xlsx) before you open your report workbooks. If you open your report workbook first, Excel finds all the sheet-scoped names that the report workbook relies on in the database workbook…and automatically deletes them.
So what’s the best way to get around this bug?
In my blog entry I offered three alternatives: Stay with Excel 2003, open database workbooks first, or don’t use sheet-scoped names in database workbooks. The last suggestion appears to be the best long-term solution.
That is, when you set up your database and report workbooks, set up your database workbook as described in Set Up Range Names to Connect Reports to Excel Databases, Part 1. Then, when you need additional databases — for targets, or for different time periods, or for logically different data, or whatever — create those databases in separate workbooks.
Then, when you open your report workbook, you can open all your database workbooks using the Edit Links dialog. To do so…
- In New Excel, choose Data, Connections, Edit Links…
- In Classic Excel, choose Edit, Links…
Finally, in the Edit Links dialog, select all of the workbooks in the list then choose Open Source. When you do so, Excel will open all those database workbooks.
That’s my best advice for now. If I come up with any better advice I’ll let you know.
