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?
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.
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.
Here is a link to the trace precedents vba example on msdn.
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.