ABC ~ All 'Bout Computers
The Online Web-azine for Computer Enthusiasts
-- brought to you by

contents page for this issue

Chad's Macro Mania
~~Chad K. Welch

 THIS MONTH'S HALLOWEEN SPECIAL
  Phantom Links in Excel 

This month’s article stems from a question from Jacki in Portland, Oregon:  I’d appreciate help with a frequent problem.  I have inherited from my predecessor many Excel files from budget to cost allocations to financial statements.  Some of them display the following message when I open them:  “The workbook you opened contains automatic links to information in another workbook.  Do you want to update the workbook with changes made to the other workbook?” If I say yes, it will usually say that the other workbook cannot be found.  I do not want to update anyway, as I do not want to maintain such links.

Sounds like you’ve got a case of the common phantom links, Jacki.  I say “common phantom links” because people run into this problem all of the time.  It is probably the single most-asked question that I get.  In answer to your question I’d like to dispel a couple myths about phantom links then I’ll show you how to find and change or remove the links. 

Myth 1:

When Excel gives this message it usually means that the file is corrupt if you can’t find any links. 

Technically, I suppose that a corrupt Excel file could erroneously display this message, but I’ve never seen it.  If Excel tells you there are links, there most likely are links. 

Myth 2:

Excel maintains a history of links, so even after deleting the link this message will be displayed. 

Excel doesn’t maintain a history unless you share your workbook and allow it to track changes.  However, even then you won’t be asked to update links that only appear in the history. 

In short, I’ve never seen an Excel workbook say it is linked when it really isn’t.  The key is just finding those phantom links. 

1. The first thing you should do is check the formulas.  Select Find from the Edit menu and try to find [.  The square bracket is usually a good indicator of an external link.
2. Select Links from the Edit menu (if it is available).  Click the change source button to have it refer to a valid file or even the active workbook.
3. Check the “series” formula for each series in any chart you have in your workbook.
4. Select the menu Insert>Name>Define and scroll through the list.  Check the “refers to” box for each name to see if it refers to an external file.

Now close and re-open your workbook.  The message shouldn’t appear any more.

The offer still stands, I’ll give $5.00 to anyone who sends me a question or idea that can be used in this article.  Send the suggestion to chad@welchkins.com with “Idea for ABC” in the subject line.

Chad K. Welch works as a technician/enabler in Utah.  He is available for consulting or application programming with Microsoft Office and VBA.  Do you have a question or tip you’d like to have Chad address in this column?  Send an email to linda@personal-computer-tutor.com or contact him directly at chad@welchkins.com for more information.