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
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.
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.
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”
Selection.Font.Bold = True
k = 2
For Each sh In ActiveWorkbook.Worksheets
For I = LBound(AllLinks) To UBound(AllLinks)
Set Rng = .Find(What:=Mid(AllLinks(I), 1, 10), _
If Not Rng Is Nothing And sh.Name <> “ExternalLinks” Then
FirstAddress = Rng.Address
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