Broken Link

[Rob Lehman]

I have a spreadsheet that shows a link to another spreadsheet (error when I open it) in a directory where the file doesn't exist. I could normally solve this problem myself, but there are no named ranges references the linked file and I can't locate any formulas referencing it either. I have also tried to break the link and it won't do that. I am using windows 7 64 bit and Excel 2010. Any tips on how I can find and remove this link?

Thanks,
Rob

 

[Asala42]

Instead of hitting "break link", try the "Change Source" button. Redirect the link back the file itself - this is usually enough to clear out phantom links. If this errors though, it means you've got an actual link somewhere.

 

[Rob Lehman]

ok...I tried the suggestion above. Didn't work (but thanks for the quick reply!). I tried all kinds of methods to break the link and couldn't get it done. Finally nailed it down that there was a recently added tab that I could delete and the link went away (even though I pasted values for that entire sheet...no formulas left...it was still preserving the link). If anyone has any thoughts on how I created the mess, let me know. I'm baffled, but I do have the problem solved.

Thanks,
Rob

 

[Asala42]

on solving the issue by narrowing down the link was on a specific worksheet.

If redirecting doesn't work, that usually means the link is not a phantom link. ie there was a link somewhere. I don't know what was on the troublesome worksheet, but some other places links can hide are:

- Conditional formats (when formulas are used)
- Data Validation - though this is rare
- Named Ranges
- Charts

Not sure if the above apply or if there was simply a remote formula somewhere on your sheet, but usually one of the above will cause this kind of thing.

 

[Callamity]

Thank you Asala42!

I've had a broken link for about 2-3 years and I've searched through every page trying to find it and no luck several times over the years, so I've just dealt with it. It must just be a Layman thing (at least phonetically), as that's my last name.

Anyway, I created a copy of the file and then I went through what you said below and had no luck, including pointing the link back to the same spreadsheet (but it failed), so I just started deleting a single sheet at a time, saving the workbook, totally closing out of XL and reopening the workbook again and again until the broken link didn't appear when I opened the file. Then I went back to the offending sheet on the real workbook and I don't know why I tried this first, but the first thing I looked at was the Data Validation (since you said it was the most rare, it had to be it, right?), and the first Data Validation drop down menu reference for some reason was linked to a temporary file on the C: drive which has been reimaged about 6 times since the link was first created.

Thanks so much...this has been annoying me for a while.

 

[Asala42]

Wow this is an old one :D