3

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?

5

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.

3
  • 1
    Thanks, 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. – Daniel T. Dec 7 '10 at 0:15
  • Also note that it does not work for cells on another worksheet that refer to the selected cell. – Firefeather Dec 7 '10 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. – Jon Peltier Sep 10 '19 at 12:49
2

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.

1

Here is a link to the trace precedents vba example on msdn.

http://msdn.microsoft.com/en-us/library/aa195797(office.11).aspx

1

Press Ctrl+Shift+} to select all dependents: cells that reference the selected cell(s).

enter image description here

1
  • 1
    This 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) – nixda Oct 7 '15 at 11:19
0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.