Excel file won't break links

Originally Posted by benalt

I found the solution. I went to File / Check for issues / Check compatibility which gave me 2 results. The second result said "One or more cells in this workbook contain data validation rules which refer to values on other worksheets...". I clicked on the "Find" button which brought me to 2 adjacent cells with pull down menus. Clicking on "Data Validation" / "Circle Invalid Data" from the "Data" tab put circles around those cells. I clicked on each cell individually and then selected "Data Validation" and it showed that the source line was referencing the other file. I manually removed the file reference from the source and then went back in to "Edit Links" and clicked on "Break Link" again and this time it removed the link!

Lesson: "Break Link" cannot remove links that are in data validation rules.

You just saved my day!!! This is by far the best solution to find broken links.

I was dealing with almost the same issue, my links were hidden in some conditional formatting that was copied over from the now-deleted file. Thanks for getting me to the right result!

I have excel 2016 and it doesn't have the File\check for issues option (or at least I can't find it). I have a "master" workbook that opens a "slave" workbook (which has some cell validation) from VBA macros. I moved the "slave" workbook to another directory and changed the "Master" VBA code accordingly. Worked fine but every time I opened the "Master" workbook, I got the "has links" message. It just would not go away and was driving me nuts. Finally after reading your post, I opened the slave workbook and removed all the cell validations. This solved the problem. THANKS for your POST!

I had a similar problem with an external link burried in a conditional formatting. None of the search methods mentioned helped so I made a copy of the whole spreadsheed then deleted all the conditional formatting on a sheet, saved, closed and re-opened the workbook. Eventually the error disappeared so I knew which sheet to look it and i found it. I must have just skipped passed in the manual search as the conditional formatting rules have become fragmented.

This was the best thread on the topic I found in a Google search for a solution so thankyou. I was on the verge of just giving up.

Thank you! This post solved the problem for me. Much appreciated!

EXCEL.jpg
HELLO , Can anyone tell me if it is possible to link the content of the box with number (10) in the "NEW FORMATING RULE" window at conditional formating >>format only top or bottom ranked values , with an external cell of the sheet ? Thanks ....

Just wanted to add to this post for any still having this issue but not finding a fix. I was also not able to break a link, and running the compatibility did not directly point out the problem. I finally found the issue in the Name Manager. There were lists that were from an external file. I deleted the list, and was able to break the link.

Originally Posted by benalt

I found the solution. I went to File / Check for issues / Check compatibility which gave me 2 results. The second result said "One or more cells in this workbook contain data validation rules which refer to values on other worksheets...". I clicked on the "Find" button which brought me to 2 adjacent cells with pull down menus. Clicking on "Data Validation" / "Circle Invalid Data" from the "Data" tab put circles around those cells. I clicked on each cell individually and then selected "Data Validation" and it showed that the source line was referencing the other file. I manually removed the file reference from the source and then went back in to "Edit Links" and clicked on "Break Link" again and this time it removed the link!

Lesson: "Break Link" cannot remove links that are in data validation rules.

It works. Awesome

It doesn't.

What are you talking about? What question? I didn't ask a question. Try again. There's nothing wrong with my post. If anything, posts like the one I responded to from Sixthsense need to be banned for good. He clearly didn't read the initial question, which said clicking "break links" doesn't work. I simply replied that his solution doesn't work. Nothing wrong at all.

No worries. Thank you so much. Just getting my feet wet here so forgive the learning curve. Thanks for all you do!

WOW THANKS! It works perfectly for me

Is anyone aware of any other places that broken links can be lurking besides within formulas, the Name Manager, Data Validation, or Conditional Formatting mentioned above? I'm trying to find a broken link in one of my inherited files and it is not coming up in any of these things.

I follow all the steps but is still have links that do not break. As final trial i started to delete sheet by sheet to try to find where the link were but this did not help. In some case the links disappeared deleting one sheet, in some cases deleting a different one. So i`m back to square one. I can`t sort this out. Any other advise?

Done! Sorry, i thought it was the opposite... I created a new post for this. thanks for your advise.

MGDASKOS was the person that asked the new question in this thread. MGDASKOS, your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Originally Posted by benalt

I found the solution. I went to File / Check for issues / Check compatibility which gave me 2 results. The second result said "One or more cells in this workbook contain data validation rules which refer to values on other worksheets...". I clicked on the "Find" button which brought me to 2 adjacent cells with pull down menus. Clicking on "Data Validation" / "Circle Invalid Data" from the "Data" tab put circles around those cells. I clicked on each cell individually and then selected "Data Validation" and it showed that the source line was referencing the other file. I manually removed the file reference from the source and then went back in to "Edit Links" and clicked on "Break Link" again and this time it removed the link!

Lesson: "Break Link" cannot remove links that are in data validation rules.

Thank you!!!!!!!!!!!! Really, really appreciated sharing this!!!!!!!!!!! Worked for me :D

I had the same type of problem it was because there were named cells from another workbook that had been deleted!

Not Working!! still unable to break the garbage links.

Broken links are there, but error is showing while removing the links. also not able to change the source location.

Please help.

Thanks,
Ankur

Great information, thanks! I've been struggling for weeks for this solution!

Originally Posted by benalt

I found the solution. I went to File / Check for issues / Check compatibility which gave me 2 results. The second result said "One or more cells in this workbook contain data validation rules which refer to values on other worksheets...". I clicked on the "Find" button which brought me to 2 adjacent cells with pull down menus. Clicking on "Data Validation" / "Circle Invalid Data" from the "Data" tab put circles around those cells. I clicked on each cell individually and then selected "Data Validation" and it showed that the source line was referencing the other file. I manually removed the file reference from the source and then went back in to "Edit Links" and clicked on "Break Link" again and this time it removed the link!

Lesson: "Break Link" cannot remove links that are in data validation rules.

Many thanks - This worked for me as well. It actually was also pointing to data validation rules that were met, and therefore didn't show up circled as well.

One tip that made it easier, was to copy the cells I removed validation for to other sheets causing the same problem in order to save time, once cleared.

I had the same issue but could still not break links after going through these suggestions. I realized that I had a Custom Sort List that was created in another workbook file. When I delete that, then it solved the problem -- no more unknown external links. Go to File > Options > Advanced > Scroll way down to the General section and click Edit Custom Lists > select the offending list > hit Delete.... or else Go to Sort & Filter > Custom Sort > Sort Order drop-down > Custom Lists > select the offending list > hit Delete > cancel the sort.

Thanks Udaman! The Name Manager was the source of my problems. Delete the range with a reference to another sheet and the link was gone.

This saved me. CRTL + F3 and delete all anmes. Thanks

Thanks the solution worked. The other solution I have found is sometimes if there is an named range that refers to another workbook it also does not allow you to break the links and you have to delete the range

For myself the only way I could fix it was to go to "Formulas" / "Defined names" / "Name Manager" and delete all the naming references