Edit Links - Break Links not working

Hello,

I have an unwanted link and I can't remove it.
I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.

Any suggestions?

Thanks,
Diana

Hello Vlady,

I did have one so I have deleted it but it still doesn't want to release the linked file.

Diana

Thanks Valdy,

That Add-In found links in the Data Validation in two places.

I ran it again and it can't find any links with the offending workbook but still it will not let me break the link in the Edit Links dialog box.

Any further ideas?
Diana

Sorry I really can't upload this file.

Diana

I had the exact same problem step by step. It still would not delete the link UNTIL I saved and re-opened the file - then it was gone.

Thanks!

Data Validation formulae are not searchable in Excel. (In other words, Find "[" in the workbook does not locate any linked workbooks in any formulae.) These OLE links are likely stored in the header information of the Excel file, in a section devoted to how to recalculate the spreadsheet (just an educated guess). Deleting an OLE link of this nature can be very difficult.

An ounce of prevention is worth a pound of cure. The moment you move or copy a worksheet from one workbook to another, IMMEDIATELY go to Data, Edit Links, and see if a link was created. If so, Change Source to the new workbook, ensuring the link disappears. ONLY THEN can you Save the workbook. If you save the workbook while the unwanted link(s) exist(s), the link will be more or less permanently embedded in the header file's recalculation rules section (again, just an educated guess).

If you are faced with one or more intractable links that cannot be deleted, here is a somewhat painful method to resolve the root cause.

1. Open the offending workbook. Starting with the worksheets most likely to contain Data Validation formulae linked to the unwanted file, start deleting those worksheets, one at a time. After each worksheet deletion, check Data, Edit Links to see if an offending link has disappeared. Continue deleting worksheets until an offending link disappears. CAREFULLY NOTE THE WORKSHEET NAME DELETION WHICH RESULTED IN THE UNWANTED LINK DISAPPEARING.
2. Close the workbook WITHOUT SAVING IT.
3. Reopen the workbook and delete ONLY the Worksheet Name that resulted in the offending link disappearing. Data, Edit Links, and see if the offending link disappeared. It might still be there if two or more worksheets contain Data Validation formulae linked to the unwanted workbook!
4. Repeat steps 1-3 until all the offending links are gone.

Now, copy or move the deleted worksheet(s) from the unwanted file(s) to the desired workbook. Ensure you use Data, Edit Links, Change Source to point to the desired workbook, and that the unwanted link is no longer present, BEFORE SAVING THE DESIRED FILE.

This procedure reliably works for me. Good luck!