|   Ah, phantom links. You open your workbook and are prompted to "Update Links," but there are no links! How can you update links when they don't exist? External links are links that reference another workbook. Unexpected external linking can occur for various reasons, many of them stemming from moving or copying charts , chart sheets, or worksheets into another workbook. Knowing why they're there doesn't always help you find them, though. Here are a few ways to deal with the spooky phantom link problem. First, you need to see whether you have any real external links (nonphantom) that you forgot about. If you are not sure whether you have real external links, start looking in the most obvious place: your formulas. You can do this by ensuring no other workbooks are open and then searching for [*] within the formulas on each worksheet. Close all other workbooks to ensure that any formula links will include [*] , where the asterisk represents a wildcard string.  | Excel 97 doesn't provide the option of searching the entire workbook, but you can search all worksheets in a workbook by grouping them. You do this by right-clicking any Sheet Name tab and choosing Select All Sheets. In later versions of Excel, Find... and Replace... provide the option of searching within the sheet or workbook. | |
Once you find the formula links, simply change the formula accordingly or delete it altogether. Whether you change the formula or delete it depends on the situation, and only you can decide which route to take. You also might want to consider going to the Microsoft Office Download Center, located at http://office.microsoft.com/Downloads/default.aspx, and from the Add-Ins category selecting the Delete Links Wizard. The Delete Links Wizard is designed to find and delete links such as defined name links, hidden name links, chart links, Microsoft query links, and object links. However, in our experience, it does not find phantom links. Once you're confident there are no formula links, you need to ensure that you don't have any nonphantom links lurking somewhere else. To do this, we like to start from within the Excel workbook containing the phantom links. Select Insert  Name Define. Scroll through the list of names, clicking to highlight each one and looking in the Refers To: box at the bottom. Check to make sure none of these names is referencing a different workbook.
If any of the names are pointing outside your workbook, you've found the source of at least one link that would prompt the updating question. Now it's up to you to decide whether you want to change this range name to refer only to the workbook itself or leave it as it is. Another potential source of links is in your charts. It's possible that your charts have the same problem we just explained. You should check that the data ranges and the X-axis labels for the chart aren't referencing an external workbook. Once again, you get to decide whether the link you've found is correct. Links also can lurk in objects, such as text boxes, autoshapes, etc. Objects can try to reference an external workbook. The easiest way to locate objects is to select any single cell on each worksheet and then select Edit Go To... (F5). From the Go To... dialog, click Special and then check the Objects option and click OK. This will select all objects on the worksheet. You should do this on a copy of your workbook. Then, with all objects selected, you can delete, save, close, and reopen your copy to see whether this has eliminated the problem. Finally, the last not-so-obvious place to check for real links is in the hidden sheets that you might have cleverly created and forgotten about. Unhide these sheets by selecting Format Sheet Unhide. If the Unhide option on the Sheet submenu is grayed out, that means you have no hidden sheets. (If you think there are sheets that don't turn up in the menu, see [Hack #5] for more information.) Now that you have eliminated the possibility of real links, it's time to eliminate the phantom links. Go to the haunted workbook with the phantom links and select Edit Links.... Sometimes you can simply select the unwanted link, click Change Source, and then refer the link back to itself. Often, though, you will be told that one of your formulas contains an error, and you will not be able to do this. If you can't take the easy way out, note to which workbook Excel thinks it is linking (we'll call it the well-behaved workbook). Create a real link between the two by opening both workbooks. Go to the problem workbook and, in any cell on any worksheet, type = . Now click a cell in the well-behaved workbook and press Enter so that you have a true external link to the other workbook. Save both workbooks, but don't close them yet. While in the phantom links workbook, select Edit Links... and use the Change Source button to refer all links to the new workbook to which you just purposely created a link. Save your workbook again and delete the cell in which you created the true external link. Finally, save your file. This often eliminates the offending phantom link, as Excel now realizes you have deleted the external link to the workbook. If this does not solve the problem, however, try these next steps, but make sure you save a copy first .  | The following process involves deleting data permanently. Therefore, before you begin, create a backup copy of your workbook. Neglecting to do so could create new problems for you. | |
With the problem workbook open, delete one sheet, Save, and then close and re-open the workbook. If you are not prompted to update your missing links, the sheet you deleted contained the phantom link. This should solve the problem, but if it doesn't, repeat the first step for each sheet in the workbook. You will need to add a new sheet before you delete the last sheet, as any workbook must have at least one sheet. We're going to assume this technique worked for you. So, here's what you should do next. Open the copy of your workbook (the one that still has data in it) and make another copy. You've got to work with the problem worksheet (or worksheets) and use the process of elimination to discover where the problem is in the worksheet. With the problem worksheet active, select a chunk of cells (about 10 x 10) and then select Edit Clear All. (Are you absolutely sure you saved a copy?) Save, close, and reopen the worksheet. If you are not prompted to update those links, you found the problem and your reward is to redo that block of cells. If you are prompted to update the links, continue deleting cells until you aren't are no longer prompted. Then redo the badly behaved cells. We hope these techniques will save you some of the frustration that arises when those dreaded phantom links appear in your workbooks. They're not easy or fun to perform, but they can get you out of trouble. |
