Tom’s Tutorials for Excel: List all link formulas’ locations and their source workbooks, sheets, and cells

Tom’s Tutorials for Excel: List all link formulas’ locations and their source workbooks, sheets, and cells.

Tom Urtis gives us another one.

For link formulas in your workbook, you can list their sheet and cell locations, AND the name of their precedent source workbook, worksheet, and cell addresses.

Sub ListSheetLinksX()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False

Dim ws As Worksheet, ListSheet$, strFormula$, xRow&, cell As Range
xRow = 3
ListSheet = "LinksList"

On Error Resume Next
Worksheets(ListSheet).Delete
Err.Clear
Sheets.Add(after:=Sheets(Sheets.Count)).Name = ListSheet

With Range("A1")
.Value = _
"List of formulas in this workbook with links to other workbooks."
Range("A2:E2").Value = _
Array _
("Worksheet in this workbook", "Worksheet cell with link formula", _
"Workbook name in link formula", "Worksheet name in link formula", _
"Link cell address")

With Range("A1:E1")
.HorizontalAlignment = xlCenterAcrossSelection
.Interior.ColorIndex = 6
End With

.CurrentRegion.Font.Bold = True
End With

For Each ws In Worksheets

On Error Resume Next
If ws.Cells.SpecialCells(3).Count = 0 Then
If Err.Number <> 0 Then Err.Clear
Else

For Each cell In ws.Cells.SpecialCells(3)
strFormula = cell.Formula
If InStr(strFormula, "[") > 0 And InStr(strFormula, "!") > 0 Then
Cells(xRow, 1).Value = ws.Name
Cells(xRow, 2).Value = cell.Address

With WorksheetFunction
Cells(xRow, 3).Value = _
Mid(strFormula, .Find("[", strFormula) + 1, .Find("]", strFormula) _
- .Find("[", strFormula) - 1)
Cells(xRow, 4).Value = _
Mid(strFormula, .Find("]", strFormula) + 1, .Find("!", strFormula) - _
.Find("]", strFormula) - 2)
Cells(xRow, 5).Value = _
Right(strFormula, Len(strFormula) - .Search("!", strFormula))
End With

xRow = xRow + 1
End If
Next cell
End If
Next ws

Cells.Columns.AutoFit

.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

MsgBox "This sheet has been updated to show the list" & vbCrLf & _
"of formulas with links to workbooks.", , "List is completed."

End Sub

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
5 comments on “Tom’s Tutorials for Excel: List all link formulas’ locations and their source workbooks, sheets, and cells
  1. Sat Khalsa says:

    This looks extremely useful! However, I get only the headings and no data on the links tab when I run it on my desired workbook. Any ideas?

    Also, it would be great to run for a single worksheet, range, or cell.

    Thanks!

    • Tom Urtis says:

      If you only get the heading and no links, maybe in that particular active workbook there are simply no links, as in references in formulas to cells in other Excel workbooks. Regarding the request for just one worksheet, you can accomplish that by deleting this line:

      For Each ws In Worksheets
      and this line
      Next ws

      and near where
      For Each ws In Worksheets
      was, type in
      Set ws = Worksheets(“YourSheetName”)

      And if you want just a range of cells, you can have
      Dim myRange as range
      Set myRange = ws.Range(“A1:X55”)

      But really, you should be seeing a list of links in the produced spreadsheet. I know the code works, so it’s curious that it is not happening for you if for sure you have link formulas. Please try it again and post back with your results.

  2. Crystal says:

    Hi Tom,

    looks handy, thanks. btw, I like to show cell.Address this way: cell.Address(false, false) ~ easier to read 😉

    Warm Regards,
    Crystal

    *
    (: have an awesome day 🙂
    *

    • Tom Urtis says:

      I often show addresses without the dollar signs as well Crystal, but I go with .Address(0,0). Same result, 8 fewer characters as I look fro any way to even out the score against the omnipresent typing gods who have pegged me as a classic typing-challenged cybergeek.

  3. JENNIFER VASSALLO says:

    great tool! thank you so much for sharing!!

Leave a Reply to JENNIFER VASSALLO Cancel reply

Your email address will not be published. Required fields are marked *

*