Can't Break Link - Try XML?

[Dman333]

I may be the epitome of a little knowledge is a dangerous thing.

I have a workbook with a link that I can’t find (Yes- I tried the search, reviewed VBA, checked all objects and name references). I finally started deleting tabs and isolated the tab. Then, I deleted everything in the tab a couple of different ways and the link wouldn’t go away. I tried to break the link but it comes back when the file is reopened.

Then I unzipped the file and started hunting through XML and found two files in the xl\externalLinks folder that had the external link reference and tried deleting them. Excel said the file was corrupt. Clearly, I’m missing other places that reference these links etc..

I’d prefer not to delete the tab and then copy it back in as it’s a model driver and there are formulas all over the place that this tab drives and I’m sure it will #REF out.

Does anyone have any ideas or come across this before? Should I even try to fix this in XML or endure the pain of rebuilding the dependent formulas?

Any advice is greatly appreciated.
Thanks,
Dman333

 

[Alex Blakenburg]

You might want to try this quite will known utility from Bill Manville
FindLink
There are quite a lot of places links can be used. eg have you looked in Named Ranges, less common is in conditional formatting, Pivot Table Source Range etc.

 

[Dman333]

You might want to try this quite will known utility from Bill Manville
FindLink
There are quite a lot of places links can be used. eg have you looked in Named Ranges, less common is in conditional formatting, Pivot Table Source Range etc.

Thanks a ton for this. I've always been able to find them when needed, so I never heard of that utility. Worked great - as it turns out it was a data validation box that I used over a year ago when setting the file up originally. Though I must say, One of the ways I deleted everything was highlighting all the columns and deleting them. But for some reason that didn't seem to eliminate the data validation drop-down box link.

In any event - Thanks for your time and recommendation. Saved me a ton of time!
Cheers

 

[Alex Blakenburg]

Thank you letting us know where the link actually turned out to be. Data Validation is not one of the places that immediately came to my mind and I am sure others will find it helpful.

Glad I could help.