This article shows how to locate cells in active workbook which contain links to external workbooks.

If your workbook contains links to other workbooks, you can locate the source files in the “Links” tab of the “Edit” Menu.

However, this will only show you the external workbook names but would not tell you where these external links are located in your spreadsheet i.e. it would not take you to the relevant cells which are referencing to those external workbooks.

In order to trace cells which contain links to other workbooks, you can use the following two options.

1) Excel Find Function
As links to external workbooks use open([) square brackets in their formula syntax which is
=Path\[file]Sheet!Cell

So, we can use [ in our find dialog box to locate the external references.

Click on Edit and then Find. Choose Options and then in the Find What box enter [.
In order to search across all sheets in a workbook, select “workbook” in the within box.
In the Look in box, choose formulas. And click Find All.
This will display a list of formulae along with the Source File Name, linked cell address, sheet name and we can click on the cell address to reach the cell directly.

This is a nice method to use as long as we do not have lots of linked cells. However, If we have a long list, then this approach will be very time consuming to look through each cell in turn.

2) A Macro to locate all the links in a workbook
The following macro will help you locate the cells which contain the links to other workbooks.

Sub ListExternalLinks()

    Dim FirstAddress As String
    Dim AllLinks As Variant
    Dim Rng As Range
    Dim I As Long
    Dim sh As Worksheet
    Dim k As Long
   
    AllLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    Sheets.Add.Name = “ExternalLinks”
    Worksheets(”ExternalLinks”).Cells(1, 1).Value = “Cell Address”
    Worksheets(”ExternalLinks”).Cells(1, 2).Value = “Sheet Name”
    Worksheets(”ExternalLinks”).Cells(1, 3).Value = “Link”
    Range(”A1:C1″).Select
    Selection.Font.Bold = True
     
    k = 2
    For Each sh In ActiveWorkbook.Worksheets
       With sh.Cells
            For I = LBound(AllLinks) To UBound(AllLinks)
                Set Rng = .Find(What:=Mid(AllLinks(I), 1, 10), _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing And sh.Name <> “ExternalLinks” Then
                    FirstAddress = Rng.Address
                    Do
                        Worksheets(”ExternalLinks”).Cells(k, 1).Value = Rng.Address
                        Worksheets(”ExternalLinks”).Cells(k, 2).Value = sh.Name
                        Worksheets(”ExternalLinks”).Cells(k, 3).Value = AllLinks(I)
                        Set Rng = .FindNext(Rng)
                        k = k + 1
                    Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
                End If
            Next I
        End With
    Next sh
End Sub
 

Popularity: 30%