is there a way to see all the tabs that are being linked to?

[bigdan]

I need to move some workbooks around and clean them up in the process. Excel has a function where you can see all the external workbooks a specific workbook relates to. Great. But how do I see all the tabs that a specific tab points to? I don't want to move a tab and then find a bunch of #N/A errors.

 

[JLGWhiz]

The trace precedents and dependents feature will tell you if the link is to another wheet, but it does not tell you which one without some additional clicking. Probably the easiest way is to use the Show Formulas feature and just look for the ones that have Sheet#! in them.

 

[bigdan]

The trace precedents and dependents feature will tell you if the link is to another wheet, but it does not tell you which one without some additional clicking. Probably the easiest way is to use the Show Formulas feature and just look for the ones that have Sheet#! in them.

that would require looking at hundreds of cells though; and these cells have large index match iserror sumif calculations so it's not even clear which tab is being referenced.

wouldnt tracing precedents only show for the specific cell i'm on, not the entire worksheet?

 

[Peter_SSs]

Firstly, just a house-keeping note: I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Not quite sure what you have/need but see if this would help. Many years ago I wrote this for listing links to other worksheets within the workbook.
I have tried to adapt it to looking for links to external worksheets. I have assumed that all external workbooks linked to are closed when the code is run. The code is not greatly tested but it should produce an extra sheet in the workbook in question that lists all external links like this. Column C should help with what you are seeking.

bigdan.xlsm
	A	B	C	D	E
1	Formula Sheet	Cell	Sheet(s) Linked To	Formula	Formula Result
2	Sheet1	A1	D:\D-Documents\MrExcel\[Daniel.xlsx]North East	=Dec!C4*'D:\D-Documents\MrExcel\[Daniel.xlsx]North East'!$F$7	150
3	Dec	F7	D:\D-Documents\MrExcel\[Raw Data.xlsm]Fees, D:\D-Documents\MrExcel\[Daniel.xlsx]Sheet2	='D:\D-Documents\MrExcel\[Raw Data.xlsm]Fees'!$B$4&" - "&'D:\D-Documents\MrExcel\[Daniel.xlsx]Sheet2'!$A$1	Late - 236
Link List

Test with a copy of your workbook.

Sub List_External_Worksheet_Links()
  Dim wsList As Worksheet, ws As Worksheet
  Dim i As Long, j As Long, nr As Long
  Dim frmlacells As Range, c As Range
  Dim RX As Object, ary As Object
  Dim s As String, t As String, fsname As String, Pat As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  On Error Resume Next
  Sheets("Link List").Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
  Set RX = CreateObject("VBscript.Regexp")
  With RX
    .Global = True
    .Pattern = "('.*?\\*?\[.*?\].*?')(?=\!)"
  End With
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Link List"
  Set wsList = ActiveSheet
  wsList.Range("A1:E1").Value = Array("Formula Sheet", "Cell", "Sheet(s) Linked To", "Formula", "Formula Result")
  wsList.Columns("A:D").NumberFormat = "@"
  nr = 1
  For i = 1 To Sheets.Count - 1
    fsname = Sheets(i).Name
    Set frmlacells = Nothing
    On Error Resume Next
    Set frmlacells = Sheets(i).UsedRange.SpecialCells(xlFormulas)
    On Error GoTo 0
    If Not frmlacells Is Nothing Then
      For Each c In frmlacells
        s = Replace(c.Formula, "=", "", 1, 1, 1)
        If RX.Test(s) Then
          t = ""
          Set ary = RX.Execute(s)
          For j = 0 To ary.Count - 1
            t = t & ", " & ary(j)
          Next j
          nr = nr + 1
          t = Replace(Replace(t, ", ", "", 1, 1, 1), "'", "", 1, -1, 1)
          With wsList.Cells(nr, 1)
            .Value = fsname
            .Offset(, 1).Value = c.Address(0, 0)
            .Offset(, 2).Value = t
            .Offset(, 3).Value = "=" & s
            .Offset(, 4).Value = c.Value
          End With
        End If
      Next c
    End If
  Next i
  wsList.Columns("A:E").AutoFit
  Application.ScreenUpdating = True
End Sub