Part IV: Using Advanced Excel Features
596
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 dif-
ferent 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:l 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.l (^) Use names rather than cell references in your link formula. This approach is the safest.