In Excel, you can tell a cell to reference another cell in order to calculate its value. I'd like to be to do the opposite, to get any cells that reference the currently-selected cell. Is this at all possible in Excel, either as one of the built-in functions or a VBA script?
6 Answers
Can't say how to use it programmatically, but from the Formulas ribbon, there is a "Formula Auditing" pane that allows you to "Trace Dependents" and will draw a visual arrow on the sheet from the currently selected cell to any that reference it via a formula.
There's a corresponding "Trace Precedents" command as well.
-
1Thanks, that worked for me. One thing to keep in mind though is that it only seems to work for the selected cell, even if you highlight more than one. Dec 7, 2010 at 0:15
-
Also note that it does not work for cells on another worksheet that refer to the selected cell. Dec 7, 2010 at 16:07
-
Actually, Trace Dependents and Precedents do tell you about links from another sheet. A dashed arrow is drawn from the cell to a small grid icon, and double-clicking on this arrow pops up a Go To dialog with a list of cells. Sep 10, 2019 at 12:49
Apparently you can access Trace Precedents/Dependents in a macro: Macro Code To Trace Dependents/Precedents @ OzGrid.com, with a link to a post with a completed VBA function @ vbaexpress.com:
Sub FindPrecedents()
' written by Bill Manville
' With edits from PaulS
' this procedure finds the cells which are the direct precedents of the active cell
Dim rLast As Range, iLinkNum As Integer, iArrowNum As Integer
Dim stMsg As String
Dim bNewArrow As Boolean
Application. ScreenUpdating = False
ActiveCell.ShowPrecedents
Set rLast = ActiveCell
iArrowNum = 1
iLinkNum = 1
bNewArrow = True
Do
Do
Application.Goto rLast
On Error Resume Next
ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum
If Err.Number > 0 Then Exit Do
On Error Goto 0
If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do
bNewArrow = False
If rLast.Worksheet.Parent.Name = ActiveCell.Worksheet.Parent.Name Then
If rLast.Worksheet.Name = ActiveCell.Parent.Name Then
' local
stMsg = stMsg & vbNewLine & Selection.Address
Else
stMsg = stMsg & vbNewLine & "'" & Selection.Parent.Name & "'!" & Selection.Address
End If
Else
' external
stMsg = stMsg & vbNewLine & Selection.Address(external:=True)
End If
iLinkNum = iLinkNum + 1 ' try another link
Loop
If bNewArrow Then Exit Do
iLinkNum = 1
bNewArrow = True
iArrowNum = iArrowNum + 1 'try another arrow
Loop
rLast.Parent.ClearArrows
Application.Goto rLast
MsgBox "Precedents are" & stMsg
Exit Sub
End Sub
FYI, the author of the second post says that this function cannot handle external references to closed workbooks. That is left as an exercise for the reader.
-
1This seems correct according to this official table of all Excel shortcuts. (However, shortcuts differ from keyboard laylout to layout. This one does not work for a german keyboard layout and haven't found the according counterpart)– nixdaOct 7, 2015 at 11:19
Here is a link to the trace precedents vba example on msdn.
http://msdn.microsoft.com/en-us/library/aa195797(office.11).aspx
In Office 2007, look under formulas and use trace precedents or trace dependents. I can't remember where it is in earlier versions but it's much the same action
On Excel 2008 (mac) it is located:
Tools > Auditing > Trace Dependents
**Typing Trace into the Help menu on the mac shows all of the Trace menu functions.*
Macros are not supported on the Mac but if a macro is written using windows version of excel then the mac version will run it. Frustrating but true.