×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

links
3

links

links

(OP)
Somehow, through copying sheets I guess, I've created a link between spreadsheets.  How does one "un-link" two spreadsheets.

Stumpy
Replies continue below

Recommended for you

RE: links

Make sure you have saved the file that you are working in (the one that has the link you need to clear).
From the pull-down menus select Edit\Links.
In the Links pop-up window select Change Source.
The Change Links window will allow you to change the link to another file.  Presumably, you will want to link to the same file you are working in so find where you saved it and double click on it.  This should clear your links to the other file.

If more than one link shows up in the Links pop-up window you will have to repeat the process.

On occassion, I've had problems clearing links this way.  Seems that I wanted to change the link from an external file to the current file and it doesn't let me so I attempt to find the link within the file I'm working to clear it directly.  I'm a little weak on this point and have not always had luck finding the link.  You can try using the Edit\Find feature and look for the characters [ or ] or ! which typically are part of the link string.  If you find it, you can then edit the cell contents as needed.

In some cases, I've been unable to use Find to find the link.  Of course it's a little more difficult when you have multiply worksheets that might contain the link though it seems Find can be used across multiple sheets.  Sometimes I wonder if links occur in places (other than cells) that I'm not aware of.  It will be interesting to see how others work this.

RE: links

(OP)
Thanks EGTO1,

Rats, I tried changing the link to the current file, got this message:

"Your formula contains an invalid external reference to a worksheet.  Verify that the path, workbook, and range name or cell reference are correct, and try again."

RE: links

3
Links can also be contained in NAMES, or defined ranges, styles and sometimes even in deleted graphs, which the methods described above can not detect.

To find the offending links in NAMES, navigate Insert, Names, Define which then shows a window with defined names. Pick each name and see at the bottom what the name refers to. Most of the references will normally be valid references, but sometimes you can see the #REF which indicates that the reference is invalid. This particular name should then be deleted.

To find the offending links in STYLES, navigate Format, Style and then in the Style Name box you can see a style name with a name of another spreadsheet appended. These should be deleted.

To check whether you've found all the bad links, navigate to Edit, Links. If Links is greyed out, it means that you've found all the bad links already. If Links is not greyed out, well, then you use the sledgehammer.

Microsoft has a utility available that identifies bad links in Excel. Go to support.microsoft.com, search the knowledgebase for dellinks.exe and follow the instructions. This utility has always worked for me.

Good luck!

Wickus

RE: links

Yes!, that's the one that always gets me!

As I mentioned, see if you can find the link using the search feature in Excel and kill it directly in the cell.

Seems like there was another trick I found that would work but it's not too pretty.  I may have to get back to you about this as it has been a while since I've had to deal with this.  Seems I had to link to another file or copy the file and link to it then try to Edit\Links to clear the link.  Maybe someone else will post a more appropriate solution.  I would like to know a better way myself.

RE: links

Didn't realize that Wickus had posted before my last post.  I got the dellinks download from MS and the dellinks add-in for Excel seems to work really nice.  Makes you wonder why MS didn't include dellinks add-in as part of their standard package for Excel.

Running the add-in will actually find the links you select and replace the link with the current cell value so you will lose any formulas by doing so.  If that is not what you want to happen, the add-in does create a copy of your original file unchanged and generates a summary report of the offending links.  This is really nice since you don't have to manually search for the bad link.  Just look in the dellink report, find where the links are and go from there.

Thanks Wickus!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close