Microsoft Office Tutorials and References
In Depth Information
Saving linked workbooks
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 you should 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”
and “Creating references to worksheets in other workbooks,” both of which appear earlier
in this chapter.
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 created and saved
a workbook named Actual that details your company’s actual (as opposed to budgeted)
expenditures. Now suppose you have another active workbook in which you are
modeling budgeted expenditures, as yet unsaved and still named Book1. Then you create linking
formulas in Actual that depend on totals in your budget workbook (Book1, still unsaved). 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]Sheet1'!$A$1.
Great, but what if that doesn’t happen? If you try to close the dependent Actual workbook
before you save the supporting Book1 (Budget) workbook, you see the “Save Actual with
references to unsaved documents?” warning. Suppose you ignore it and click OK to save
and close the workbook. 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 displays the security warning in Figure 12-36, alerting you
that links are present that cannot be updated (because Book1 does not exist). Click the Edit
Links button to display the dialog box shown in Figure 12-37.
Excel is, of course, unable to find Book1. You need to click the Change Source button to
locate the Actual workbook so that Excel can reestablish the links.
Figure 12-36 If an external link cannot be found, Excel requires your intervention.
Search JabSto ::

Custom Search