List dependents (not display arrows)

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,225,477
Messages
6,185,213
Members
453,283
Latest member
Shortm88

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