Parse sheet names from within existing formulas

[gmhumphr]

I am working with an existing financial model workbook that has a whole mess of formulas and cross references. I want to try to get my arms around the dependencies and links between worksheets in the model. I am capable of writing some parsing formulas in excel, but they wouldn't be able to handle multiple sheets in a single formula, or odd and complex placement of single word named tabs (i.e. not bound between single quotes).

Question: Given the text of a formula (e.g. =Inputs!K5), how can I extract the all worksheets that are being referenced in the formula (assume that all formulas only reference to sheets within the current workbook)? Worksheet names may be one word or multiple (thus having/not having single quotes around the name). Worksheet references may be buried pretty deep within nested formulas. It is important to specifically return these results for each cell in question (not just find the aggregate cross references for the sheet).

Example: A1 contains the formula: =VLOOKUP($A3,Inputs!$K$5:$J$20,2,0)+'Rpt Schedule'!$A$3, which returns some value; the desired formula entered in B1 would return the text string: Inputs, Rpt Schedule (also amenable to these being displayed separately in additional columns if easier, e.g. B1 = Inputs, C1 = Rpt Schedule).

Barring a better solution, I'm listing all worksheets along the top and doing a search function to check existence.

 

[Dryver14]

Have you tried using the trace dependents and trace precendents on the formulas tab

 

[offthelip]

I am not sure how you want the results displayed but this sub will find all the references to worksheets within the same workbook:

Sub test()
Dim wsnames As Variant
Dim inarr As Variant
 Dim serchtxt As String
 Dim formstr As String
 
   ws_count = ActiveWorkbook.Worksheets.Count
' loop through all worksheets
ReDim wsnames(1 To ws_count, 1 To 100)
For i = 1 To ws_count
   wsnames(i, 1) = ActiveWorkbook.Worksheets(i).Name
   wsnames(i, 2) = ""
Next i


For i = 1 To ws_count
    ActiveWorkbook.Worksheets(i).Select
     inarr = Worksheets(i).UsedRange.Formula
     Lrow = UBound(inarr, 1)
     lcol = UBound(inarr, 2)
     For j = 1 To ws_count
        serchtxt = wsnames(j, 1) & "!"
        For k = 1 To Lrow
          For m = 1 To lcol
            If Left(inarr(k, m), 1) = "=" Then
             formstr = inarr(k, m)
             If InStr(2, formstr, serchtxt, 0) Then
             wsnames(i, 2) = wsnames(i, 2) & wsnames(j, 1) & "(" & k & "," & m & ")"
             End If
           End If
          Next m
        Next k
     Next j
Next i


    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
    End With
ActiveWorkbook.Worksheets("temp").Select
Range(Cells(1, 1), Cells(ws_count, 100)) = wsnames
End Sub

 

[gmhumphr]

This works for dealing with a handful of formulas (and what I've been doing so far). This model has so many connections that I am trying to tackle them all at once by parsing out the formulas, and then doing aggregation on the results. I'm trying to get the relevant sheets for every off sheet reference in the workbook.

 

[gmhumphr]

Thanks for the code, the input, and the thought process! I'm still working on reading through the code to understand exactly what it does. I ran it cold and it created the Temp tab and entered sheet names plus cell reference (R1C1) with no delimitation between elements. This solution is jumping ahead of my specific request (though rightfully so, since I'm clearly working toward this end). The serchtxt function where you pass in the list of worksheet names on the front end is a slick way to search for worksheet references instead of some sort of RegEx that has to handle forward looking based on "!" and "'", I may play with that element some more.

 

[offthelip]

I realised after posting my code that it didn't handle tab names with spaces correctly ( i.e not at all!!)
change the line:

serchtxt = wsnames(j, 1) & "!"

to

        serchtxt = wsnames(j, 1)       
       If InStr(1, serchtxt, Chr(32), 0) Then
         serchtxt = serchtxt & "'!"
        Else
        serchtxt = serchtxt & "'!"
        End If

this code should handle the whole workbook with lots of equations without any problems , this is because everything is loaded into variant arrays. so it minimises accesses to the worksheet so even though it searches through every cell in the workbook it won't take long.

 

[Peter_SSs]

Example: A1 contains the formula: =VLOOKUP($A3,Inputs!$K$5:$J$20,2,0)+'Rpt Schedule'!$A$3, which returns some value; the desired formula entered in B1 would return the text string: Inputs, Rpt Schedule

Welcome to the MrExcel board!

I think the code I provided in this thread does exactly what you want (& possibly a bit more). It creates a new worksheet 'Link List' in the workbook with the link details.

Here is my 'Link List' after running the code with the example setup you gave above.

Book1
	A	B	C	D	E
1	Formula Sheet	Cell	Sheet(s) Linked To	Formula	Formula Result
2	Sheet1	A1	Inputs, Rpt Schedule	=VLOOKUP($A3,Inputs!$J$5:$K$20,2,0)+'Rpt Schedule'!$A$3	579
Link List

 

[gmhumphr]

Thanks Peter - the link you provided really encapsulates the full extent of what I am aiming for (not just the subset I was specifically asking for in this post).

 

[Peter_SSs]

Thanks Peter - the link you provided really encapsulates the full extent of what I am aiming for (not just the subset I was specifically asking for in this post).

You are welcome. Good to recycle that code after all this time.