Rid Excel worksheets of outdated links




Whenever I open a particular Excel workbook, I get a message asking if I want to update the links. I've examined every formula in the workbook and am certain it contains no links to any other file. How can I convince Excel of this?
- Tim Buehler


I've never known Excel to be wrong about identifying links, so there's an excellent chance your workbook does contain one or more links -- but they are probably not formula links. These steps will identify and remove any links in a workbook.
1. Select Edit--Links. In many cases, this command may not be available. If it is available, the Links dialogue box will tell you the name of the source file for the link.
2. Click the Change Source button and change the link to the active file.

Check the names in your workbook for outdated links to other files

Charts can also harbour absolete links. Check each data series

3. Select Insert--Name--Define. Scroll down the list in the Define Name dialogue box and examine the Refers to box. Delete names that refer to another workbook or that contain an erroneous reference (such as #REF!).
4. If you have a chart in your workbook, click on each data series in the chart and examine the SERIES formula in the formula bar. If the formula refers to another workbook, you've identified the link. To eliminate it, move the chart's data into the current workbook and re-create your chart.
5. If your workbook contains any custom dialogue boxes, select each object in each dialogue box and examine the formula bar. If any object contains a reference to another workbook, edit or delete that reference.
6. Save your workbook. When you reopen it, Excel won't ask you to update links.
- John Walkenbach


Category: Spreadsheet
Issue: Jan 1998
Pages: 166-167

These Web pages are produced by Australian PC World © 1997 IDG Communications