Finding phantom links
By Neale Blackwood
Have you ever opened an Excel file and had the question come up – Do you want to update links? You say – there shouldn’t be any links to other sheets in the workbook.
Well, there are a number of potential causes. One is a reference to another file in a range name. When you copy sheets between files and there are range names involved the range names will often retain their links to the original file.
To identify these names try these steps:
1. Insert a new blank sheet
2. Click cell A1 and press the F3 function key and click the Paste List button
3. Scan column B for external references. If the list is long then use Ctrl + f and find [
That should identify the phantom links in the range names.
You can then amend or delete the names – remember they maybe used in calculations. To Edit names hold the Ctrl and press F3 function key.
Note: You can use step 3 on your sheets to identify external links in cell formula.
There is a free add-in that automatically finds links.
See our Downloads page