Chapter 28: Linking and Consolidating Worksheets
28
It’s easy to determine the source workbooks for a particular dependent workbook: just examine the files listed in the
Edit Links dialog box (choose Data ➪ Queries & Connections ➪ Edit Links). However, it’s not possible to determine
whether a particular workbook is used as the source for another workbook.
You can avoid this problem by doing the following:■ (^) Always opening the dependent workbook(s) when you modify the source work-
book: If you do so, Excel adjusts the external references in the dependent work-
book when you make changes to the source workbook.
■ Using names rather than cell references in your link formula: This approach is
the safest.
■ Using a formula to refer to a cell: Your source workbook may not be structured to
allow this method.
The following link formula refers to cell C21 on Sheet1 in the budget.xlsx workbook:
=[budget.xlsx]Sheet1!$C$21
If cell C21 is named Total, you can write the formula using that name:
=budget.xlsx!Total
Using a name ensures that the link retrieves the correct value, even if you add or delete
rows or columns from the source workbook.
By the way, notice that the filename isn’t enclosed in brackets. That’s because Total is
assumed to be a workbook-level name and doesn’t need to be qualified with a sheet name. If
Total were a sheet-level name (defined on Sheet1), the formula would be as follows:
=[budget.xlsx]Sheet1!Total
See Chapter 4, “Working with Excel Ranges and Tables,” for more information about creating names for
cells and ranges.
If your source workbook contains a list of months and values and you want to return the
July value, you can use a formula such as the following:
=VLOOKUP("July",source.xlsx!MonthValues,2,FALSE)
The source workbook has a workbook-level name MonthValues. If you used a normal cell
reference, you would still have a problem if rows were inserted into the source workbook.
However, by naming the entire range, you don’t have to make a named range for each
month separately.