Part IV: Using Advanced Excel Features

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 workbooks). 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.1

This 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 reference 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.

•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 you can read about links.

592

Chapter 27: Linking and Consolidating Worksheets

Security Warning for Links

Excel 2010 features a new security feature. The first time you open a workbook that contains links to other files, you see a security warning below the Ribbon. The links will not be updated unless you click the Enable Content button.

However, Excel remembers that you’ve deemed the workbook safe, so you won’t see that Security Warning again. If you would like to disable these Security Warnings, use the External Content tab of the Trust Center dialog box and change the option for Security Settings for Workbook Links. To display this dialog box, choose File Options. Then click the Trust Center tab, and click the Trust Center Settings button.

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 its Edit Links dialog box, shown in Figure 27.3. Click the Change Source button to specify a different workbook, or click the Break Link to destroy the link.

Note

You can also access the Edit Links dialog box by choosing File Info Related Documents Edit Links to Files. The dialog box that appears lists all source workbooks, plus other types of links to other documents. n

FIGURE 27.2

Excel displays this dialog box when you open a workbook that contains links to other files.

593

Part IV: Using Advanced Excel Features

FIGURE 27.3

The Edit Links dialog box.

Changing the startup prompt

When you open a workbook that contains one or more external reference formulas, Excel, by default, displays the dialog box (shown in Figure 27.2) that asks how you want to handle the links. You can eliminate this prompt by changing a setting in the Startup Prompt dialog box (see Figure 27.4).

To display the Startup Prompt dialog box, choose File Info Related Documents Edit Links to Files, which displays the Edit Links dialog box (refer to Figure 27.3). There, click the Startup Prompt button and then select the option that describes how you want to handle the links.

FIGURE 27.4

Use the Startup Prompt dialog box to specify how Excel handles links when the workbook is opened.

Updating links

If you want to ensure that your link formulas have the latest values from their source workbooks, you can force an update. For example, say that you just discovered that someone made changes to the source workbook and saved the latest version to your network server. In such a case, you may want to update the links to display the current data.

To update linked formulas with their current value, open the Edit Links dialog box (choose File Info Related Documents Edit Links to Files), choose the appropriate source workbook in the

594

Chapter 27: Linking and Consolidating Worksheets

list, and then click the Update Values button. Excel updates the link formulas with the latest version of the source workbook.

Note

Excel always sets worksheet links to the Automatic Update option in the Edit Links dialog box, and you can’t change them to Manual, which means that Excel updates the links only when you open the workbook. Excel doesn’t automatically update links when the source file changes (unless the source workbook is open). n

Changing the link source

In some cases, you may need to change the source workbook for your external references. For example, say you have a worksheet that has links to a file named Preliminary Budget, but you later receive a finalized version named Final Budget.

You can change the link source using the Edit Links dialog box (choose File Info Related Documents Edit Links to Files). Select the source workbook that you want to change and click the Change Source button. Excel displays its Change Source dialog box, from which you can select a new source file. After you select the file, all external reference formulas that referred to the old file are updated.

Severing links

If you have external references in a workbook and then decide that you no longer need the links, you can convert the external reference formulas to values, thereby severing the links. To do so, access the Edit Links dialog box (choose File Info Related Documents Edit Links to Files), select the linked file in the list, and then click Break Link.

Caution

Excel prompts you to verify your intentions because you can’t undo this operation. n

Avoiding Potential Problems with

External Reference Formulas

Using external reference formulas can be quite useful, but the links may be unintentionally severed. As long as the source file hasn’t been deleted, you can almost always re-establish lost links. If you open the workbook and Excel can’t locate the file, you see a dialog box that enables you to specify the workbook and re-create the links. You also can change the source file by using the Change Source button in the Edit Links dialog box. The following sections discuss some pointers that you must remember when you use external reference formulas.

595

Part IV: Using Advanced Excel Features

Renaming or moving a source workbook

If you rename the source document or move it to a different folder, Excel won’t be able to update the links. You need to use the Edit Links dialog box and specify the new source document. See “Changing the link source,” earlier in this chapter.

Note

If the source and dependent folder reside in the same folder, you can move both of the files to a different folder. In such a case, the links remain intact. n

Using the Save As command

If both the source workbook and the dependent workbook are open, Excel doesn’t display the full path to the source file in the external reference formulas. If you use the File Save As command to give the source workbook a new name, Excel modifies the external references to use the new workbook name. In some cases, this change may be what you want. But in other cases, it may not.

Here’s an example of how using File Save As can cause a problem: You finished working on a source workbook and save the file. Then you decide to be safe and make a backup copy on a different drive, using File Save As. The formulas in the dependent workbook now refer to the backup copy, not the original source file. This is not what you want.

Bottom line? Be careful when you choose File Save As with a workbook that is the source of a link in another open workbook.

Modifying a source workbook

If you open a workbook that is a source workbook for another workbook, be extremely careful if the dependent workbook isn’t open. For example, if you add a new row to the source workbook, the cells all move down one row. When you open the dependent workbook, it continues to use the old cell references — which is probably not what you want.

Note

It’s easy to determine the source workbooks for a particular dependent workbook: Just examine the files listed in the Edit Links dialog box (choose File Info Related Documents Edit Links to Files). However, it’s not possible to determine whether a particular workbook is used as the source for another workbook. n

You can avoid this problem in the following ways:

•Always open the dependent workbook(s) when you modify the source workbook. If you do so, Excel adjusts the external references in the dependent workbook when you make changes to the source workbook.

•Use names rather than cell references in your link formula. This approach is the safest.

596