Linking Workbooks

Creating dynamic links between workbooks using external reference formulas provides a number of advantages. For example, you could break a large, complex company budget model into more manageable departmental models. Then you could link all the departmental workbooks (supporting workbooks) to a master budget workbook (a dependent workbook). In addition to creating more manageable and flexible models, linked workbooks can save recalculation time and memory.

The following sections discuss some special considerations to be aware of when working with workbooks linked by external reference formulas. For more information about external references, see "Creating References to Other Worksheets in the Same Workbook" on page 430 and "Creating References to Worksheets in Other Workbooks" on page 430.

Saving Linked Workbooks

When you create a set of linked workbooks, you should save the supporting workbooks before you save the dependent workbooks. For example, suppose you are modeling your company's 2008 budget in an unsaved workbook called Book1. When you save the workbook, you give it the name Budget.

Now suppose you have another active workbook in which you plan to enter actual (as opposed to budgeted) expenditures; you have already saved the workbook with the name Actual. This workbook contains links to your Budget workbook and, therefore, depends on the Budget workbook for some of its information. When you first created these links, the Budget workbook was identified as Book1.

If you save Book1 as Budget while the Actual workbook is still open, all references to Book1 in the Actual workbook change automatically to Budget. For example, if Actual contains the reference =[Book1]Sheet1!$A$1, the reference changes to ='[Budget.xlsx]Sheetr1$A$1.

If you try to close the dependent Actual workbook before you save the supporting Book1 (Budget) workbook, however, you see the "Save Actual with references to unsaved documents?" warning. Click OK to save and close it. When you then save Book1 as Budget, Excel doesn't update the references to Book1 in the Actual workbook because it isn't open; the formulas continue to reference Book1. When you reopen Actual, Excel first displays a security warning, alerting you that automatic links are present. Click the Options button to display the dialog box shown in Figure 12-36, where you can select Enable This Content to allow linking formulas to function.

Figure 12-36: Excel disables external links by default, requiring your intervention.

After you enable links, Excel then displays a message box that prompts you to update the linked information. If you click the Edit Links button, the dialog box shown in Figure 12-37 appears.

Figure 12-37: Use the Edit Links dialog box to manage all your external links.

Excel is, of course, unable to find Book1. You need to click the Change Source button to locate the Actual workbook so Excel can reestablish the links.

Opening a Dependent Workbook

When you save a workbook that contains dependent formulas, Excel stores the most recent results of those formulas. If you open and edit the supporting workbook after closing the dependent workbook, the values of edited cells in the supporting workbook might be different. When you open the dependent workbook again, the workbook contains the old values of the external references in the dependent formulas, and Excel displays a security warning, alerting you that automatic links are present. Click the Options button to display the dialog box shown in Figure 12-36, where you can select Enable This Content to update the linked formulas. Excel then searches for the supporting workbook. If it finds the workbook, Excel reads the supporting values and updates the dependent formulas in the dependent workbook. Excel does not open the supporting workbook; it merely reads the appropriate values from it.

If Excel can't find the supporting workbook, it displays the alert "This workbook contains one or more links that cannot be updated." You can click Continue to open the workbook anyway, or you can click the Edit Links button to display the dialog box shown in Figure 12-37.

Editing Links

You can open supporting workbooks, as well as specify different supporting workbooks, when you click the Edit Links button, located in the Connections group on the Data tab on the Ribbon. When you do so, a dialog box like the one shown in Figure 12-37. Here is some helpful information about using the Edit Links dialog box:

• An A in the Status column indicates a link that is updated automatically.

• An M in the Status column indicates a manual link that isn't updated until you click Update Values.

• Click Open Source to open the supporting workbook.

• Click Change Source to select a different supporting workbook.

• Click Break Link to convert all existing external references in formulas to their current values. You can't undo this action, so proceed with caution.

• Click Update Values to fetch the latest figures from the supporting workbook without having to open it.

• You can link objects and documents created in other applications, such as Word, to Excel worksheets and charts. When you do so, the Type column displays the application name and the object type.

Clicking the Startup Prompt button displays the Startup Prompt dialog box shown in Figure 12-38, which you can use to specify how links are handled whenever the workbook is opened.

Figure 12-38: The Startup Prompt dialog box lets you customize the startup behavior of external links.

Ordinarily, Excel displays a security alert when you open a workbook containing linking formulas, which individual Excel users can choose to suppress on their computers. If you would prefer to suppress the security alert for the current workbook, you can do so by selecting either Don't Display The Alert And Don't Update Automatic Links or Don't Display The Alert And Update Links in the Startup Prompt dialog box.

Note 

To change the default behavior of disabling automatic links, click the Microsoft Office Button, Excel Options, Advanced category, and then in the General section, clear the Ask To Update Automatic Links option.

Copying, Cutting, and Pasting in Linked Workbooks

You can use relative or absolute references to cells in other workbooks as you do in a single workbook. Relative and absolute references to cells in supporting workbooks respond to the Copy, Cut, and Paste commands and toolbar buttons in much the same way as references to cells in the same workbook do.

For example, suppose you type the formula =[Form2.xlsx]Sheet1!F1 in cell A1 on Sheet1 of Form1 and then use Copy and Paste to copy this formula to cell B1. The formula in cell B1 becomes =[Form2.xlsx]Sheet1!G1. The original formula changes when you copy it to cell B1 because the reference to cell F1 is relative. However, if the formula in cell AT of Form1 contained an absolute reference, such as =[Form2.xlsx]Sheet1!$F$1, the reference in the copied formula would not change.

Copying and Pasting Between Workbooks

When you copy a dependent formula from one workbook to another and that formula includes a relative reference to a third workbook, Excel adjusts the reference to reflect the new position of the formula. For example, suppose that cell A1 in Form1 contains the formula =[Form2.xlsx]Sheet1!A1. If you copy and paste that formula into cell B5 in Form3, the result is the formula =[Form2.xlsx]Sheet1!B5. Excel adjusts the formula to reflect its new relative position.

If you copy a formula that contains an absolute reference to another workbook, the formula remains the same. For example, suppose cell A1 in Form1 contains the formula =[Form2.xlsx]Sheet1!$A$1. If you copy and paste that formula into cell B5 in Form3, the resulting formula is the same.

Even if you copy a dependent formula to the workbook to which the formula refers, it's still a dependent formula. For example, if you copy the formula =[Form2.xlsx]Sheet1!$A$1 from cell A1 of Form1 to cell A3 on Sheet1 of Form2, the resulting formula is essentially the same, except that the book reference isn't necessary because the formula is in the same workbook. As a result, the formula becomes =Sheet1!$A$1.

Cutting and Pasting Between Workbooks

Excel does not adjust the relative references in a formula when you cut it from one workbook and paste it in another, as it does when you copy a formula. For example, suppose that cell A1 on Sheet1 of Form1 contains the formula =[Form2.xlsx]Sheet1!A1. If you cut that formula and paste it into cell B5 of Form3, the formula does not change.

When you cut and paste cells, Excel usually adjusts any references to those cells in the formulas of the workbook. Dependent formulas, however, do not follow the same rules. When you cut and paste a cell referred to by a dependent formula in a closed workbook, that formula isn't adjusted to reflect the change.

For example, suppose you create the formula =[Form2.xlsx]Sheet1!A10 in cell A1 in Form1. If you close Form1 and use Cut and Paste to move the entry to cell B10 of Form2, the formula in cell A1 of Form1 remains the same. You might expect the link to be broken because the worksheet containing the formula was closed when you modified the referenced cell. However, Excel manages to keep track of everything.