In Set Up Range Names to Connect Reports to Excel Databases, Part 1 we saw why it’s a bad idea for formulas in one workbook to reference cell addresses in another workbook. Instead, your formulas should link to range names in the database workbook, sort of like this:
A1: =SUM(Database.xls!Data)
This is an improvement over referencing cell addresses, but it’s not the end of the story. Here’s why:
When you see a short reference like “Database.xls!Data” in your formula then you know that Excel recognizes the database workbook as one that is open in Excel. But if formulas in your report workbook don’t recognize the database as an open workbook, the references in your formulas will look something like this:
A1: =SUM(‘E:\Data\MyStuff\Database.xls’!Data)
At times, these links can become damaged. Perhaps you move the database file on your hard drive. Perhaps you rename the file. Perhaps you move the report workbook to a different computer. Perhaps you accidentally damage the link text with a search-and-replace command. Whatever.
This damage could be a minor problem or a major problem, depending on how you set up your report workbook.
It could become a major problem if your report workbook has thousands of formulas with damaged links to your database workbook. But broken external links become a minor problem if we take one more step…
Open the Report.xls and Database.xls workbooks introduced in Part 1. In Report.xls create the range name DbData and define the name as:
=Database.xls!Data
Then, in Report.xls, enter this formula for the cell shown:
A1: =SUM(DbData)
Consider what we’ve done with this simple change.
We’ve created a range name in the report workbook that points to another range name in the database workbook. Then we use that new name in our formulas. If the link between the two workbooks becomes damaged, we only need to fix the definition of that one range name; we don’t have to fix the many formulas that would otherwise have used the external link. And if we create several range names like this in the database workbook, those names are easy to find and fix because we begin all those names with “Db” (for “database”), as in “DbData”.
Also, of course, when our formulas in the report workbook look something like…
=SUM(DbData)
…they’re shorter and easier to use than formulas like…
=SUM(Database.xls!Data)
So here’s the bottom line for this step: Set up references to other workbooks in your range names, not in your formulas. Formulas that use those names will be easier to use, and any problems with your links will be much easier to fix.
Finally, there’s one last thing we need to consider. What do we do when we want to maintain several worksheets with data in one database workbook? I’ll take up that topic in Set Up Range Names to Connect Reports to Excel Databases, Part 3.
