Links to other worksheets (XP)

Topic

New Reply

When I copy a worksheet from one file to another, I sometimes unknowingly create links to the original file because of some of the formulas on the sheet.
When I open the new worksheet, I often get the message asking if I want to update links to the other file, or let them be. Since I want the formulas to refer to cells in their new file, I then search everywhere to find and eliminate the links.
Sometimes they’re in the defined names, sometimes they’re in a formula somewhere. Sometimes it takes a half-hour to find them all.

I can’t find a way for Excel to show me where the links are. When I have eliminated them all, I no longer get the message. That’s the only indication I have.

Does anyone have a better way ? I’d sure appreciate it.

Reply | Quote

Replies

A manual way to change all links in one go:

– Save your new workbook
– Edit, links
– Select the link
– Choose change source
– Browse to the file you just saved.
– OK your way out and save.

Reply | Quote

Navigator Utilities has a Link Navigator which lists not only the source workbooks but all the link references such as cells, links hidden in named ranges, nasty links hiding in charts or behind buttons as references to macros in other workbooks, and lots of other links which are hard to find. Links are easily deleted in the Link Navigator.

You can also get a report of all linked files from a directory of workbooks and even do a global find/replace of link paths when many files are moved to say a different server and all the links are broken!

Navigator Utilities will also help you navigate sheets and named ranges and also has an enhanced find/replace utility. Find it at http://www.robbo.com.au.

regards

Reply | Quote

Thanks to all of you for your replies. Now I can’t wait for the next time I get this problem, so I can whack it !!

Reply | Quote

Whack it in about 8 ways, kevin, just to make sure!

Reply | Quote

(Edited by HansV to update link to Excel MVP site)

You can use Edit | Links… to see how many external links there are in your workbook. If there are none, this menu item is disabled (grayed out).
You can also use Jan Karel Pieterse’s Name Manager add-in or Bill Manville’s FindLink add-in. Both are free and can be downloaded from the Excel MVP page

Reply | Quote

(Edited by Leif on 13-Mar-04 16:45. to update link to http://www.bmsltd.ie/)

Bill Manville’s FindLink utility sounds like just what you need.

Reply | Quote