Posts tagged as:

External references

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… [click to continue...]

{ Comments on this entry are closed }

Andrew Bates left a comment in Set Up Range Names to Connect Reports to Excel Databases, Part 1, asking how to report and analyze variable-length data. The problem he describes is really common. I used to have it a lot when I worked with data exported as text files and imported into Excel. But once I worked out a solution, it saved me a lot of time. [click to continue...]

{ Comments on this entry are closed }

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: [click to continue...]

{ Comments on this entry are closed }

Set Up Range Names to Connect Reports to Excel Databases, Part 1

August 25, 2009

When a formula in one workbook references a range in another workbook, always reference a range name, never a cell address.

Read the full article →

Excel Bug Deletes Some External Range Names Automatically

August 24, 2009

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 in the database workbook.

Read the full article →