David rightly notes that Excel provides auditing tools (Trace Dependents and Trace Precedents) that are a very helpful way of keeping track of what is happening in large worksheets. However, the actual interface just lists out the cells in a small area, and David cannot easily copy out this list of cells to analyze and manipulate it. When he uses Trace Dependents on an important cell in a large worksheet, the small dialog box can contain several hundred references. David wonders if there is a relatively easy way of getting this information into a more usable format, like a blank worksheet or another workbook.
There is obviously no way to do this with native Excel commands, but you can create a macro that will extract the information you desire. The following macro will list the dependent cells for whatever cell is selected when you run the macro:
Sub ListDependents() Dim rArea As Range Dim rCell As Range Dim rDep As Range Dim lRow As Long Dim sCellAddr As String sCellAddr = ActiveCell.Address(False, False) On Error Resume Next Set rDep = ActiveCell.Dependents If rDep Is Nothing Then MsgBox sCellAddr & " has no dependents" Exit Sub End If On Error GoTo 0 Worksheets.Add lRow = 1 Cells(lRow, 1).Value = "Dependents for " & sCellAddr For Each rArea In rDep For Each rCell In rArea lRow = lRow + 1 Cells(lRow, 1) = rCell.Address(False, False) Next Next Set rArea = Nothing Set rCell = Nothing Set rDep = Nothing End Sub
When the macro is first run, it checks to see if there are any dependents for the cell. If there aren't, then you are notified and the macro is exited. If there are dependents, then a new worksheet is added to the workbook and the dependents of the cell are added to the worksheet.
If you want the macro to instead list precedents, all you need to do is change the all instances of "Dependents" in the macro to "Precedents."
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8271) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Accessing Dependent and Precedent Information.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Want to create your own add-in? Excel makes it easy to do. Here are all the steps you need.
Discover MoreThe Convert Text to Columns capabilities of Excel are very helpful when pulling apart information. When working with ...
Discover MoreThe primary way to extend what Excel can do is through the use of add-ins. This tip explains what they are and the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2018-03-28 19:47:15
Darren E
AndrewMB, Saskia,
Excel help for the Range.Dependents property is clear:
"The Dependents property only works on the active sheet and can not trace remote references."
For clarity the code should be modified:
MsgBox sCellAddr & " has no dependents on the active sheet"
Cells(lRow, 1).Value = "Dependents for " & sCellAddr & " on the active sheet " &activesheet.name
2018-03-28 08:06:31
Alan Cannon
This macro will only work if the worksheet is unprotected. If it is protected the macro gives the " ... no dependents ..." output.
2018-03-28 02:42:08
Saskia
It only works on the current worksheet. If a cell is referring to a cell on another worksheet, it says that there are no dependents.
2013-10-14 17:36:15
Mark D
Hi Chris
I know if you double click on the arrow to an offsheet precedent it will pop up a box with the cell reference.
That's the limit of my knowledge, maybe someone knows how to take this further.
Mark
2013-10-12 10:14:57
AndrewMB
This macro appears to only list the dependent cells on the same worksheet and does not list dependent cells from other worksheets on the same workbook. Typically, when there are alot of dependent cells, many are from other worksheets. Can the macro be modified to look at all of the dependent cells on the same workbook?
2013-10-12 05:58:02
Chris S
Am I missing something?
At the risk of looking foolish...
The article seems to suggest that the normal ribbon interface enables you to list (in a text form which can be copied) the cells which depend on a particular cell. But I can only see a graphical (arrow) display....
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2018 Sharon Parq Associates, Inc.
Comments