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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Hans W. Herber

New Member
Joined
Sep 16, 2002
Messages
4
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

New Member
Joined
Sep 16, 2002
Messages
4
... change the Row:
If InStr(rng.Formula, "\[") Then

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

please

hans
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

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

Well-known Member
Joined
Mar 13, 2002
Messages
555
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,987
Members
422,256
Latest member
downeybm

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
Top