This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with Links in Formulas
If you have data in one workbook that you want to use in another, you can set up a link between them. This action enables your formulas to use references to cells or ranges in the other workbook. When the other data changes, Excel automatically updates the link.
For example, Figure 3.11 shows two linked workbooks. The Budget Summary sheet in the 2005 Budget—Summary workbook includes data from the Details worksheet in the 2005 Budget workbook. Specifically, the formula shown for cell B2 in 2005 Budget—Summary contains an external reference to cell R7 in the Details worksheet of 2005 Budget. If the value in R7 changes, Excel immediately updates the 2005 Budget—Summary workbook.
)
Figure
3.11 These two workbooks are linked because the formula in cell B2 of the
2005 Budget—Summary workbook references cell R7 in the 2005 Budget
workbook.
NOTE
The workbook that contains the external reference is called the dependent workbook (or the client workbook). The workbook that contains the original data is called the source workbook (or the server workbook).
Understanding External References
There's no big mystery behind these links. You set up links by including an external reference to a cell or range in another workbook (or in another worksheet from the same workbook). In the example shown in Figure 3.11, all I did was enter an equals sign in cell B2 of the Budget Summary worksheet, and then click cell R7 in the Details worksheet.
The only thing you need to be comfortable with is the structure of an external reference. Here's the syntax:
'path[workbookname]sheetname'!reference
|
path |
The drive and directory in which the workbook is located. You need to include the path only when the workbook is closed. |
|
workbookname |
The name of the workbook, including an extension. Always enclose the workbook name in square brackets ([ ]). You can omit workbookname if you're referencing a cell or range in another sheet of the same workbook. |
|
sheetname |
The name of the worksheet's tab. You can omit sheetname if reference is a defined name in the same workbook. |
|
reference |
A cell or range reference, or a defined name. |
For example, if you close the 2005 Budget workbook, Excel automatically changes the external reference shown in Figure 3.11 to this (depending on the actual path of the file):
='C:\My Documents\Worksheets\[2005 Budget.xls]Details'!$R$7
NOTE
You need the single quotation marks around the path, workbook name, and sheet name only if the workbook is closed or if the path, workbook, or sheet name contains spaces. If in doubt, include the single quotation marks anyway; Excel happily ignores them if they're not required.
Updating Links
The purpose of a link is to avoid duplicating formulas and data in multiple worksheets. If one workbook contains the information you need, you can use a link to reference the data without re-creating it in another workbook.
To be useful, however, the data in the dependent workbook should always reflect what actually is in the source workbook. You can make sure of this by updating the link, as explained here:
If both the source and the dependent workbooks are open, Excel automatically updates the link whenever the data in the source file changes.
If the source workbook is open when you open the dependent workbook, Excel automatically updates the links again.
If the source workbook is closed when you open the dependent workbook, Excel displays a dialog box asking whether you want to update the links. Click Yes to update or No to cancel.
If you didn't update a link when you opened the dependent document, you can update it any time by selecting the Edit, Links command. In the Edit Links dialog box that appears (see Figure 3.12), highlight the link and then click Update Values.
)
Figure
3.12 Use the Edit Links dialog box to update the linked data in the source
workbook.
Editing Links
If the name of the source document changes, you'll need to edit the link to keep the data up-to-date. You can edit the external reference directly, or you can change the source by following these steps:
With the dependent workbook active, choose Edit, Links to display the Edit Links dialog box.
Select the link you want to change.
Click Change Source. Excel displays the Change Source dialog box.
Find and then choose the new source document, and then click OK to return to the Edit Links dialog box.
Click Close to return to the workbook.