Excel 2010 - Can't Remove Links

[3gswish]

Hi everyone,

I've gone through all of the previous posts regarding removing bad links, but the previous posts are pretty stale, and most of the links in them are no longer working, or refer to code/add-ins that no longer work in Excel 2010, so I hope you don't mind my restarting this topic in a new thread

I have tried all of the suggestions in them anyway, and have only partially resolved my issue.

I am working in a workbook that contains 10 worksheets with varying in row count from 100 to 1000.

A number of them were copied from a couple of other workbooks. At the time I copied the data (either by doing the move/copy worksheet or cut/paste) I was not aware of links and how hard they would be to deal with after the fact!

The edit links dialog was referencing 2 different files.

I was able to delete all of the named ranges which cleared up one of the links in the edit links dialog.

There remains a link to the second file, with Status = Unknown, which I cannot get rid of !!!!

When I click Break Link, I get a response that this convert to values, and when I click Break Links in that popup, the popup goes away without complaint, yet the link remains.

I have searched for "[" and "]" in the workbook, with no results found.
I have searched for part of the linked filename in the workbook with no results found.
I have tried the microsoft DeleteLink add-in - it doesn't seem to work in 2010.
I have tried finding objects via a macro that was posted years ago, it errors and must not be good for Excel 2010.

I have spent more time trying to figure this out than it would have taken to simply cut/paste values into a new spreadsheet.
(Which I can do, as this workbook only contains values and minimal formatting)

My problem is that I am unable to let go of a problem until I find a proper solution!

Thank you in advance for any input you would like to provide

 

[Arik_Fogel]

I had the same problem, apparently that Excel can't disconnect links that placed on auto design formulas
go check it...
hope it was helpfull!

 

[sika85]

Thanks all for your responses. Here is what I did

Systematic elimination is the only solution! Follow below mentioned steps if you could not get rid of External Links!

In my case it was a problem of copying a sheet from other workbook and the remains of ref were found in conditional formatting! Excel should do better in copy/move sheet operation, similar to "Paste Special" but currently it does not.

Ok, so here is what I did and what you can do

0. I assume you have all tried with 'Find all with "[" and "!"' and eliminated all the cells in workbook for these contents and still you are facing this issue. Also I assume you are not having any other external links in the file before. You can still use these instructions but it may be more tedious to work on.

1. Identify when the problem started, in most cases the last copying sheet operation would have brought in this insect. If you do not know or do not have a clue about this, and if you have many sheets, it would be harder but patience is the key!

2. Make a copy of the excel file you are having and now on use this copy itself for experiment

3. Delete the sheets from the workbook that you feel are creating issue and save the file.

4. Re-open the file, goto DATA Ribbon and see if the "EDIT Link" option is enabled. If not, you have found the sheets that have links. If its still enabled, you deleted the wrong sheets.

5. You get a fresh copy and try again with differernt sheets till you zero in for the problematic sheet.

6. Now, you should have a copy of file with the problematic sheet removed and with no links dialog. Add a blank sheet at the place where the sheet was deleted and give the same name for the sheet

7. Open the original workbook and select all from the sheet, come to the blank sheet and Paste Special with option "Column Width". This will set the column widths according to original sheet.

8. After every step, check if Edit Link option is being enabled under DATA Ribbon, if it is not, Save the experimental sheet

9. Now use "Paste Special" to copy formula

10. Finally use "Paste Special" to copy Formatting

11. From steps 7 till 10, at some point, your "Edit Link" option will become enabled, when it does, Close file without saving and reopen to get the file back in without "Edit Link" enabled.

12. You have just narrowed your problem to formula or formatting.

13. Now use divide and conquer rule by copying parts of cells from the original sheet to know which cells are causing the issue and you will soon understand the problem cell and eliminate the same.

14. Once you know the root cause, go back to the original sheet and remove the problem causing elements such as formula, conditional formatting or any other issue and you will become Link FREE!!

Although the steps are many, actual time may not take more than 15-20 minutes to exeperiment. Hope this helps!

This finally worked, thanks!

 

[bendwalk]

I found this thread via a google search when I had a similar issue. I figured out my problem, but the answer for me wasn't listed here, so I wanted to add another thing to check in case someone in the future googles this.

My issue ended up being cells which had data validation that referenced another workbook which had been moved/deleted. To find which cells have data validation: https://support.office.com/en-US/ar...lidation-f9ac75b6-1092-4250-b2e7-13f5857ad075

 

[ginipig]

konew1,
Yes mostly it is seen that it is a problem with one or couple of sheets, because it starts appearing due to a recent modification to the file and for some days typically people ignore it thinking its some random error and eventually go away. When it doesn't and people become irritated then they resolve to find a solution but by the time, they would have forgotten about what changes they made so far!

What you suggested is also a robust solution of copying one sheet at a time, which can be used when you have no clue or background of that file at all. But you can cut down on time, if you have a clue of sheets that got recently added/changed and target them first to resolve.

 

[jarabr]

Hi, I got similar problem - broken links and whaever I did nothing worked for removing them. At the end I finished with checking every single cell copying and pasting into another sheet as values and when I wanted to have the same colours as in the broken sheet I clicked for the colour of the cell and found out that there is no colour selected. Even though the cell was green. That gave me an idea of CONDITIONAL FORMATTING! BINGO!
I opened the conditional formatting and some of my stupid predecessors who created the sheet, set the conditional formatting based on the value which was stored in the external sheet. after deleting this I was finally able to remove the broken link. Hope this will help all the desperate people

 

[daycartes]

I also had this problem. I knew the offending data as I assumed that it was when I did a copy and paste. My solution was to insert six new columns,copy the six offending columns and paste into notepad. This will get rid of links, then copy and paste into the recently inserted columns from notepad, then delete the offending six columns.
That worked!
Thanks to everyone who posted on here.

 

[benevide]

Hi, I got similar problem - broken links and whaever I did nothing worked for removing them. At the end I finished with checking every single cell copying and pasting into another sheet as values and when I wanted to have the same colours as in the broken sheet I clicked for the colour of the cell and found out that there is no colour selected. Even though the cell was green. That gave me an idea of CONDITIONAL FORMATTING! BINGO!
I opened the conditional formatting and some of my stupid predecessors who created the sheet, set the conditional formatting based on the value which was stored in the external sheet. after deleting this I was finally able to remove the broken link. Hope this will help all the desperate people

Thank you for sharing. In my case it was a problem with DATA VALIDATION. In some cells data validation was referencing another file that didn't exist. After cleaning up data validation I could copy and paste the values and formatting to another worksheet and it worked just fine (links were not there any more).