List dependents (not display arrows)

[BigC]

I have a workbook with a number of sheets in it - the whole workbook being used last financial year end (2001) to calculate various values for inclusion in my client's annual report to shareholders.

Because of the size and complexity of the model, it is a little tricky to update or "roll" it to cater for this year end (i.e. to change the links to new precedent files for 2002)and to know what other cells, sheets, workbooks are DEPENDENT upon this workbook.

Is there a tool or macro which can list (perhaps on a separate sheet) the path, filename, sheetname, & cell reference of all the links which are DEPENDENT upon each cell in a selected range within my workbook? (i.e. What I need is the reverse of Edit / Links - identifying dependents rather than precedents - and with more detail.)

Any assistance will be greatly appreciated.
Thanks
BigC

 

[Hans W. Herber]

the Code:

<pre>
Sub LinkInfo()
Dim arrDetails As Variant
Dim rng As Range, rngSel As Range
Dim iCounter As Integer, iRow As Integer, sText As String
Set rngSel = Selection
Workbooks.Add 1
Range("A1").Value = "LinkAddress:"
Range("B1").Value = "Path:"
Range("C1").Value = "Workbook:"
Range("D1").Value = "Worksheet:"
Range("E1").Value = "Range:"
Range("A1:E1").Font.Bold = True
iRow = 1
For Each rng In rngSel
If rng.HasFormula Then
If InStr(rng.Formula, "[") Then
arrDetails = GetDetails(rng.Formula)
iRow = iRow + 1
Cells(iRow, 1).Value = rng.Address
For iCounter = 1 To 4
Cells(iRow, iCounter + 1).Value = arrDetails(iCounter)
Next iCounter
End If
End If
Next rng
Columns.AutoFit
End Sub

Private Function GetDetails(sTxt As String) As Variant
Dim sWkb As String, sWks As String, sRng As String
Dim sPath As String
Dim arr(1 To 4) As String
arr(1) = Mid(sTxt, InStr(sTxt, "'") + 1, _
InStr(sTxt, "[") - InStr(sTxt, "'") - 2)
arr(2) = Mid(sTxt, InStr(sTxt, "[") + 1, _
InStr(sTxt, "]") - InStr(sTxt, "[") - 1)
arr(3) = Mid(sTxt, InStr(sTxt, "]") + 1, _
InStr(sTxt, "'!") - InStr(sTxt, "]") - 1)
arr(4) = Right(sTxt, Len(sTxt) - InStr(sTxt, "!"))
GetDetails = arr
End Function
</pre>

You find a sample workbook here:
http://www.herber.de/bbs/texte/10listlinks.xls

hans

 

[Hans W. Herber]

... change the Row:
If InStr(rng.Formula, "\[") Then

in:
If InStr(rng.Formula, "[") Then

please

hans

 

[PaddyD]

Hans,

FYI - there's a bug on the board that always posts up two back-slashes for every one you write. Most annoying!

Paddy

P.S. Welcome!!

 

[BigC]

Hans/ Paddy

Thanks for the prompt response, however... even after Paddy has described the bug, I'm still not clear how many back slashes are required in that line of code (0, 1, 2 or 4??)
It also appears that I must have the dependent workbooks open - but if I knew which ones I had to open I would be half way there!

Even then, despite experimenting with different combo's of "", I'm still not getting the answer I'm after (ie. the identity of the workbook, etc. which is using cells in the selected range as a source). I get a new sheet with the column headings, but no link info, yet I know another workbook is dependent on cells in my active workbook!!

Thanks again
BigC

 

[sen_edp]

Hello Everyone

Sorry for jumping in, just trying to help

There is a nice addin from Aaron Blood
called explode that i use it very often for auditing and checking purposes , and it is free

http://www.xl-logic.com/pages/explode.html

hth