Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Fixing Formula Boo-Boos
The Edit Links dialog box gives you other options on how to handle broken
links. Click on the Data tab on the Ribbon and click on Edit Links in the
Connections section. Doing this opens the Edit Links dialog, as shown in
The buttons along the right side of the dialog box work like this:
✓ Update Values: When external workbooks are where they should be,
this action gets the values from the external workbooks, and the cells
with those formulas are recalculated. When there are broken links, an
Open File type of dialog box appears from which you browse to a file
from which to get the values. This does not necessarily have to be the
missing workbook — it could be another workbook. A point to be aware
of is that using Update Values in this manner does not fix the link. It
helps you get values but does not change the way formulas are written.
Instead, use the Change Source option, listed next.
✓ Change Source: This option displays an Open File type of dialog box that
lets you select an external workbook to use. Selecting a workbook in this
dialog box actually alters the formula that references the external
workbook. So, this is the best course to take to permanently fix a broken link.
✓ Open Source: In the case of broken links, this action does nothing
because the source (the external workbook) cannot be found. An error
message will confirm this. In the case of working links, this action opens
the workbook referenced in the link.
✓ Break Link: This action converts formulas that contain external links
to the calculated values. In other words, the cells that contain formulas
with external links are replaced with a value; the formulas are removed.
Make sure this is what you want to do. You cannot undo this action, and
it can be a serious mistake if you did this unintentionally. Excel displays
a confirmation of this, shown in Figure 4-8.