+ Reply to Thread
Results 1 to 35 of 35

Excel file won't break links

  1. #1
    Registered User
    Join Date
    03-02-2011
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Excel file won't break links

    Hello,

    I copied 2 worksheets from one file to another and went in to "Edit Links" and changed the source to itself rather than the original file which took away those links in the cells that the original file was present in, but it still gives me a dialogue when opening the file that links to the original file. I go back to "Edit Links" and press "Break Link" but it doesn't seem to remove the link to the original file. If I click on "Connections", none are present. I clicked on "Startup Prompt" and set it so that it wouldn't annoy me with the prompt any more, but I'd ideally like to not have any reference to the original file. What can I do to accomplish this?

    Thanks,
    Ben
    Last edited by benalt; 07-16-2014 at 10:03 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: Excel file won't break links

    Clicking Break Link should break the external references link.

    If possible share a screenshot of breaklink window


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Registered User
    Join Date
    03-02-2011
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Excel file won't break links

    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.
    Last edited by benalt; 07-16-2014 at 10:22 AM.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: Excel file won't break links

    Glad you fixed it and thanks for sharing the solution with us

  5. #5
    Registered User
    Join Date
    02-24-2012
    Location
    Moon
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Excel file won't break links

    Quote Originally Posted by benalt View Post
    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.

  6. #6
    Registered User
    Join Date
    11-24-2015
    Location
    USA
    MS-Off Ver
    2013
    Posts
    1

    Re: Excel file won't break 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!

  7. #7
    Forum Contributor
    Join Date
    07-23-2016
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    267

    Re: Excel file won't break links

    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!

  8. #8
    Registered User
    Join Date
    09-19-2016
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2010
    Posts
    3

    Re: Excel file won't break links

    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.

  9. #9
    Registered User
    Join Date
    09-23-2016
    Location
    Brooklyn, NYC
    MS-Off Ver
    365 on MAC
    Posts
    1

    Re: Excel file won't break links

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

  10. #10
    Registered User
    Join Date
    09-23-2016
    Location
    greece
    MS-Off Ver
    2010
    Posts
    1

    Re: Excel file won't break links

    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 ....

  11. #11
    Registered User
    Join Date
    02-24-2016
    Location
    Canton, Ohio
    MS-Off Ver
    2013
    Posts
    24

    Re: Excel file won't break links

    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.

  12. #12
    Registered User
    Join Date
    03-22-2017
    Location
    Hanoi, Viet Nam
    MS-Off Ver
    2016
    Posts
    1

    Re: Excel file won't break links

    Quote Originally Posted by benalt View Post
    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

  13. #13
    Registered User
    Join Date
    04-25-2017
    Location
    Fort Worth, Texas
    MS-Off Ver
    Office 2016
    Posts
    3

    Re: Excel file won't break links

    It doesn't.

  14. #14
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,117

    Re: Excel file won't break links

    chrismsmith_fw,
    Unfortunately 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.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ben Van Johnson

  15. #15
    Registered User
    Join Date
    04-25-2017
    Location
    Fort Worth, Texas
    MS-Off Ver
    Office 2016
    Posts
    3

    Re: Excel file won't break links

    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.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,069

    Re: Excel file won't break links

    The thread is over three years old, and is marked as solved.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  17. #17
    Registered User
    Join Date
    04-25-2017
    Location
    Fort Worth, Texas
    MS-Off Ver
    Office 2016
    Posts
    3

    Re: Excel file won't break links

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

  18. #18
    Registered User
    Join Date
    08-01-2017
    Location
    Bandung, Indonesia
    MS-Off Ver
    2016
    Posts
    1

    Thumbs up Re: Excel file won't break links

    WOW THANKS! It works perfectly for me

  19. #19
    Registered User
    Join Date
    06-29-2017
    Location
    Los Angeles, CA
    MS-Off Ver
    2010
    Posts
    43

    Re: Excel file won't break links

    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.

  20. #20
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,117

    Re: Excel file won't break links

    jdriscol,
    Unfortunately 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.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  21. #21
    Registered User
    Join Date
    01-04-2018
    Location
    Tenerife
    MS-Off Ver
    Office360
    Posts
    7

    Re: Excel file won't break links

    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?

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,069

    Re: Excel file won't break links

    @Topacciolo - unfortunately 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.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  23. #23
    Registered User
    Join Date
    01-04-2018
    Location
    Tenerife
    MS-Off Ver
    Office360
    Posts
    7

    Re: Excel file won't break links

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

  24. #24
    Registered User
    Join Date
    08-03-2018
    Location
    White Lake, Michigan
    MS-Off Ver
    2016
    Posts
    1

    Re: Excel file won't break links

    Here's how I fixed mine:

    FIND THE CULPRIT:
    1) Make a work copy of your problem file
    2) Systematically delete one sheet, save/close the work file, then reopen the file
    a) If the message appears upon opening, delete another sheet, save/close and open the file
    b) repeat this process until the error no longer appears upon opening the file
    c) Make note of the sheet that was deleted that caused the pesky message to stop appearing.
    3) Once the error stops appearing, go back to the original problematic fie and save it as the work version again. Now you know what sheet needs to be fixed.
    NOTE) If there is no conditional formatting on the problematic sheet, this solution is not for you.

    THE FIX
    4) Click on "Conditional Formatting" on the Styles ribbon to open the Conditional Formatting Rules Manager window
    5) From the dropdown, select "This Worksheet"
    6) For each rule, click "Edit Rule" and be sure none of them reference a file. You should be able to cleanly delete any rules that do. Don't try to fix them...they are extraneous.
    7) Save this work file, close it, and reopen it to make sure the error doesn't appear. If it does, rinse and repeat.
    8) If all is well, you can safely overwrite the problematic version with the fixed work version.

    Note: I guess it may be possible to have more than one sheet be a problem. Use your analysis skills to identify sheets that are culprits if the one-by-one system doesn't get it for you.

    GOOD LUCK! The developers should provide some sort of assistance to finding these little hidden problems.
    Last edited by songjockey; 08-03-2018 at 11:37 AM. Reason: clarity

  25. #25
    Registered User
    Join Date
    12-20-2013
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    83

    Re: Excel file won't break links

    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.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,069

    Re: Excel file won't break links

    Thanks for spotting this, however the post in question is two years old and was the only post ever made by that member. As such, it does not require any follow-up.

  27. #27
    Registered User
    Join Date
    10-03-2018
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    1

    Re: Excel file won't break links

    Quote Originally Posted by benalt View Post
    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

  28. #28
    Registered User
    Join Date
    01-24-2019
    Location
    Indiana
    MS-Off Ver
    Office 2010
    Posts
    1

    Re: Excel file won't break links

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

  29. #29
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,207

    Re: Excel file won't break links

    Quote Originally Posted by pburgeson View Post
    I had the same type of problem it was because there were named cells from another workbook that had been deleted!
    Thanks for the feedback on this
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  30. #30
    Registered User
    Join Date
    07-10-2014
    Location
    Gurgaon, India
    MS-Off Ver
    2007, 2010
    Posts
    1

    Re: Excel file won't break links

    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

  31. #31
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,207

    Re: Excel file won't break links

    Ankur.newid welcome to the forum

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

  32. #32
    Registered User
    Join Date
    03-16-2016
    Location
    London
    MS-Off Ver
    2013
    Posts
    34

    Re: Excel file won't break links

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

  33. #33
    Registered User
    Join Date
    02-25-2019
    Location
    NYC
    MS-Off Ver
    1902
    Posts
    1

    Re: Excel file won't break links

    Quote Originally Posted by benalt View Post
    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.

  34. #34
    Registered User
    Join Date
    07-23-2019
    Location
    Seattle, WA
    MS-Off Ver
    Office365
    Posts
    1

    Re: Excel file won't break links

    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.

  35. #35
    Registered User
    Join Date
    12-03-2013
    Location
    Adelaide Australia
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Excel file won't break links

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Similar Threads

  1. Cannot Break links in excel
    By jennyc_83 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-23-2014, 07:39 AM
  2. [SOLVED] Hyperlinks - Move file to local drive, all links break
    By Shawn McGowen in forum Excel General
    Replies: 1
    Last Post: 03-08-2006, 08:52 PM
  3. break excel links
    By striker72s in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-02-2005, 09:05 AM
  4. [SOLVED] Want Excel to break links between worksheets in same file
    By eclectic_kx in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-25-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1