Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Links to other worksheets (XP)

    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. <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to other worksheets (XP)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to other worksheets (XP)

    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 www.robbo.com.au.

    regards

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    Quebec City, Quebec, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to other worksheets (XP)

    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 !!

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to other worksheets (XP)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Links to other worksheets (XP)

    (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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Links to other worksheets (XP)

    <P ID="edit" class=small>(Edited by Leif on 13-Mar-04 16:45. to update link to http://www.bmsltd.ie/)</P>Bill Manville's FindLink utility sounds like just what you need.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •