Bdtyktl

One place that these links can "hide" in is named ranges. Excel is only breaking links inside formulas. To fix named ranges, Go to Formulas->Name Manager and see if you have outside workbooks referenced there. You can shift-click to select a bunch and delete them.

I just had this exact problem, and found another broken link location not mentioned here: Data Validation (otherwise known as drop down menus).

Similar to finding "Objects" in the Microsoft support, go under "Home (Tab) -> Editing (Group) -> Find & Select -> Go To Special... -> Data Validation" to find all cells with data validation on the worksheet. Then you will need to more or less check the Data Validation cells one by one, which you do by clicking one of the Data Validation cells, then going under "Data (Tab) -> Data Tools (Group) -> Data Validation -> Settings -> Source." When updating the Source, be sure to check the box "Apply these changes to all other cells with the same settings" to change all sources that are the same.

Here's a solution that has been very reliable for me:

1. Change the file extension of your .xlsx file to .zip
   


2. Unzip this zip file in a new folder


3. Using Explorer, in that folder, search for the file name in the file CONTENTS.

Note: Sometimes the name is written in URL format, e.g., %20 instead of spaces, so you might consider using a simplified string that is found only in the problematic file. I used last word of the filename + .xlsx.

4. It will probably return sheetN.xml as a result. This is the file corresponding to your problematic sheet, where N is the number of the problematic sheet in the order they are displayed. Open it in a text editor such as Notepad.




5. Find the search string and look at the code around it; it should point you to some cell references that you can lookup in the problematic sheet.

Note: As pointed out before, it could also be in Conditional Formatting or Data Validation.

After checking objects, formulas, Names, chart titles and data series, I discovered that my external reference was in "Conditional Formatting". However, there was no reference to another workbook, such as [Budget.xls] in any of the fields or conditions. Only afterward was I able to successfully break the link in the "Connections" Group. I hope this helps!

It's probably simpler than that. The period is the end of the sentence in help. You should just be searching for the open square bracket. In other words, search for:

[

not for

[.

in formulas. The latter will only find a square bracket followed by a period. Excel find doesn't do regular expressions.

i found "Dan G" entry was my issue. A validation cell with a link to another sheet.
However I had a few hundred to go through, I found using Dan G's search method worked but with a little twist.

Find a validation cell that is valid, select "Home (Tab) -> Editing (Group) -> Find & Select -> Go To Special... -> Data Validation" but select the "Same" radio button under Data Validation to find all cells with the same data validation on the worksheet.

The one that is causing you the issue should stand out as it won't be highlighted/selected.

I was having similar external link issues in excel 2016. I have tried various methods but finally succeeded by data validation tab under data tab. I have clicked "Circle Invalid data" and immediately noted two drop down menu was pointing towards the worksheet from where I coped data from. After sourcing the issue, immediately I could update the data within the work book. Problem solved...

To summarize (and to add an answer I just stumbled across), look at:

• Formulas (search for "[")


• Name Manager


• Hidden worksheets


• Cells having Conditional Formatting


• Cells having Data Validation


• Objects


• Buttons that call macros (i.e. is that macro in THIS workbook?)

On any sheet, the last three can be reached easily by F5 --> "Special..."

Save, close and reopen the file to update the "Edit Link" list, otherwise the links may still appear even if the source has been successfully removed.

The brute force method would make a copy of the workbook, delete every suspicious object, button, and even worksheet, one at a time, and see when the "Edit Link" window shows that the troublesome link is no more.

For Excel 2010 or higher, follow these steps.

1. Click the Options Command.


2. Then Hit Advanced.

Under section General ,

3. Click to clear the "Ask to update automatic links" check box.

NB: When the Ask to update automatic links check box is cleared, the links are automatically updated. And no such message appears. This option applies to the current user only and affects every workbook that the current user opens, if Other users of the same workbooks are not affected.

Other method is to Update the links using Commands, follow these steps.

In Excel 2010 or higher

Click Edit Links in the Connections group on the Data tab.

Click Update Values and Click Close.

Hope this Help you.

I wrote a quick macro to find a validation rule that links to an excel spreadsheet:

Public Sub FindExtValidation()



    Set s1 = ActiveSheet        

    Set v1 = s1.Cells.SpecialCells(xlCellTypeAllValidation)

    For i = 1 To s1.UsedRange.Rows.Count

        For j = 1 To s1.UsedRange.Columns.Count

            If Not Intersect(v1, s1.Cells(i, j)) Is Nothing Then

                If InStr(s1.Cells(i, j).Validation.Formula1, ".xl") > 0 Then

                    s1.Cells(i, j).Activate

                    Exit Sub

                End If

            End If

valid_error:

        Next

    Next

End Sub

It fails if the current sheet has no validation at all. Not sure how to fix that, don't really care enough to try.

I had an Excel file that when opened displayed a message regarding a missing external link.
No such link could be found and various tools- Kutools, FormulaDesk etc did find nothing.
Finally- I solved the problem by opening the xlsx file as zip+xml and deleting the folder dealing with external links (if you want more details- ask me).

I found my zombie links were stashed in the conditional formatting formulas. As soon as i found and removed all, I was able to break the links.

1. On the Home tab, select the Conditional Formatting tool


2. At the bottom of the list select manage rules


3. At the very top is a pull down for each sheet in your workbook, defaulted to the Current Selection. (If nothing is selected then it defaults to the current sheet from which you accessed the conditional formatting tools)


4. Examine any of the formula based conditions looking for references to outside documents. Delete the ones that are no longer valid.


5. From the pull down at the top, select the next sheet until you have eliminated all invalid links used in the conditional formatting.