Excel – way to tell which cells reference a selected cell in Excel

microsoft excelvbaworksheet-function

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?

Best Answer

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.

Related Solutions

Excel – Copy cell colour from one worksheet to another based on cell reference in second sheet

This is a pretty simple VBA code, but does the job:

Sub CopyColors()
    For i = 1 To Cells(Rows.Count, 2).End(xlUp).Row
        Sheet2.Range("B" & i).Interior.Color = Sheet1.Range(Sheet2.Range("B" & i).Text).Interior.Color
    Next i
End Sub

Cells(Rows.Count, 2).End(xlUp).Row finds the last row in column B that contains data, the rest is pretty straightforward. Of course Sheet2 refers to sheet "Comments", Sheet1 to "Attendance". You can also use Range("Comments!B" & i) instead of Sheet2.Range("B" & i), then inserting a new sheet has no effect on the references (just changing the sheet names).

Maybe it can be done with conditional formatting (without a macro), but I think that is really painful especially if you have lots of possible colours. I am not even sure if it is possible.

EDIT: You should paste the code to the workbook's module in VB editor, like here

Best Answer

Related Solutions

Excel – Copy cell colour from one worksheet to another based on cell reference in second sheet

Related Question