Chapter 28: Linking and Consolidating Worksheets
28
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 Data ➪ Queries &
Connections ➪ Edit Links 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 28.2) that asks you what to do. Your options are as follows:Update The links are updated with the current information in the source file(s).
Don’t Update The links are not updated, and the workbook displays the previous values
returned by the link formulas.
Help The Excel Help screen displays so that you can read about links.To prevent Excel from displaying the dialog box shown in Figure 28.2, open the Excel Options dialog box, select the
Advanced tab, and in the General section, remove the check mark from Ask to Update Automated Links. That dis-
ables the dialog box for all workbooks.
FIGURE 28.2
Excel displays this dialog box when you open a workbook that contains links to other files.What if you choose to update the links but the source workbook is no longer available? If
Excel can’t locate a source workbook that’s referred to in a link formula, it displays a dialog
box with two choices:Continue Open the workbook, but don’t update the links.
Edit Links Display the Edit Links dialog box, shown in Figure 28.3. Click the Change
Source button to specify a different workbook, or click the Break Link button to destroy
the link and keep the current values.