Identify all sheets related to one sheet

jazmin

New Member
Joined
Oct 13, 2011
Messages
3
Hi:
I have been given an excel model to review;the workbook has 53 sheets. I would like to know if there is a way to create a flowchart/matrix with the relation of all the sheets. I would like at least to get a list of all the sheets related to each sheet. All what I was able to do is to get all the sheet names in one sheet. (I am using Excel 2003)
Many thanks!!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Welcome to the MrExcel board!

Not sure if this is more (or less) than you want or if there may be an easier way.

I would suggest testing in a copy of a workbook that has a relatively small number of sheets and links.

It only attempts to check for formula links between sheets of the workbook itself, not for links to other workbooks.

If any of your sheets are large and may have more than 8,192 separate areas of formulas, then further work will be required. Ref: http://www.rondebruin.nl/specialcells.htm

Anyway, give it a try.

VBA Code:
Sub List_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
  For Each ws In ThisWorkbook.Worksheets
    Pat = Pat & "'*|'*" & ws.Name
  Next ws
  Pat = "(" & Replace(Pat, "'*|", "", 1, 1, 1) & "'*)(?=\!)"
  Set RX = CreateObject("VBscript.Regexp")
  With RX
    .Global = True
    .Pattern = Pat
  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


It should return results along these lines on a separate 'Link List' sheet:

Excel Workbook
ABCDE
1Formula SheetCellSheet(s) Linked ToFormulaFormula Result
2Main DataA1Summary=Summary!A1+5168.1
3Main DataB6xyz, Some other sheet name=A1&" "&xyz!B2&" "&'Some other sheet name'!F4168.1 dollars spent this week
4SummaryA1abc, xyz=abc!A1*xyz!F4163.1
5abcB4xyz=xyz!F423.3
Link List
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,263
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top