Part IV: Using Advanced Excel Features
592
Working with External Reference Formulas
This section discusses some key points that you need to know about when working with links.
Understanding these details can help prevent some common errors.Creating links to unsaved workbooks
Excel enables you to create link formulas to unsaved workbooks (and even to nonexistent work-
books). Assume that you have two workbooks open (Book1 and Book2), and you haven’t saved
either of them. If you create a link formula to Book1 in Book2 and then save Book2, Excel displays
the confirmation dialog box shown in Figure 27.1.Typically, you don’t want to save a workbook that has links to an unsaved document. To avoid this
prompt, save the source workbook first.FIGURE 27.1This confirmation message indicates that the workbook you’re saving contains references to a workbook
that you haven’t yet saved.
You also can create links to documents that don’t exist. You may want to do so if you’ll be using a
source workbook from a colleague, but the file hasn’t yet arrived. When you enter an external ref-
erence formula that refers to a nonexistent workbook, Excel displays its Update Values dialog box,
which resembles the Open dialog box. If you click Cancel, the formula retains the workbook name
that you entered, but it returns a #REF! error.When the source workbook becomes available, you can choose File ➪ Info ➪ Related Documents ➪
Edit Links to Files to update the link; see “Updating links,” later in this chapter) After doing so, the
error goes away, and the formula displays its proper value.Opening a workbook with external reference formulas
When you open a workbook that contains links, Excel displays a dialog box (shown in Figure
27.2) that asks you what to do.l Update: The links are updated with the current information in the source file(s).l (^) Don’t Update: The links are not updated, and the workbook displays the previous values
returned by the link formulas.
l (^) Help: The Excel Help screen displays so you can read about links.