Using external links — linking one workbook to data in another workbook — can be a great idea or a horrible idea. It all depends on how you do it.
It’s a great idea if you keep your data in a database workbook and then report your data in one or more report workbooks. This lets you update your data once and use it many times.
But to make this idea work well, you need to set up your links correctly. So let’s see out how to do this…
Open two new workbooks. Save one as Database.xls and save the other as Report.xls. In Database.xls, enter the values 1, 2, and 3 in cells A1 through A3, respectively. In Report.xls, enter this formula for the cell shown:
A1: =SUM([Database.xls]Sheet1!$A$1:$A$3)
When you press Enter, cell A1 displays 6, the sum of 1+2+3. Now save both workbooks and then close Report.xls.
In Database.xls, insert two rows at the top of your worksheet so that your data is in the range A3:A5. Now open Report.xls again. When you recalculate, cell A1 of your report now displays the value 1, because the formula in cell A1 of Report.xls still refers to the range A1:A3.
And that’s a bad thing, of course.
To fix this problem, we assign a range name to our data in Database.xls, and then we use the range name — not the cell references — in our formulas in Report.xls. To assign the range name, select the range of data (A3:A5) in Database.xls, and then…
- In New Excel, choose Formulas, Defined Names, Define Name…
- In Classic Excel, choose Insert, Name, Define…
…enter Data as the name in the dialog, and then choose OK.
In Report.xls, replace your formula with this one for the cell shown:
A1: =SUM(Database.xls!Data)
And save both workbooks.
Now, you can move the range of data in Database.xls anywhere you want in the workbook, and the formulas in Report.xls will continue to return the correct results.
Here’s the lesson: When a formula in one workbook references a range in another workbook, always reference a range name, never a cell address.
That’s not the end of the story, however. This is because even if the formulas in our report use range names we still can have other problems with our links. We’ll discuss those problems and their solutions in Set Up Range Names to Connect Reports to Excel Databases, Part 2.
{ 3 comments }
Have you found any easy way (read automatic way) of linking a reporting spreadsheet to a data spreadsheet (generated externally) where the number of rows is not known in advance (and changes on each invocation)? For example, I have a data sheet containing information about all my customers, one customer per row. I want a summary sheet that counts the number of customers (number of rows in the table), the sum of their YTD sales (contained in a column in the data table), and a count of the customers we have done business with in the last 12 months (last invoice date is another data element in the table).
Despite a programming background, I haven’t found a function reference or cell reference formula that seems to return the data I need to reference back to the underlying data table.
Oh yes – I’m using Excel 2007 with Lists. Thanks for any help you can provide or any resources you can point me to.
Charlie. Nice solution!! Many thanks for the guidance. It really helped.
Andrew,
Take a look at How to Report and Analyze Variable-Length Databases in Excel.
Charley