How to Trace Dependents Across Sheets in Excel (2 Easy Ways)

To illustrate how to trace dependencies, we will use the following data set. Here in columns B and C, we have some order ids and their corresponding products.

Metho1 – Using Trace Dependents Command to Trace Dependents Across Sheets

Steps:

• Take two worksheets to make a data set. As we will show trace dependent across sheets, we will need at least two worksheets.
• We made the data set in the Trace Dependent sheet.

• We created another worksheet and named it Trace Dependent 1.
• We will make an extra column to apply a formula that will contain the cell addresses from both sheets.
• Copy the following formula in cell D5:

• Press Enter to see the result.
• Use the AutoFill feature to show the results for the lower cells as well.

• Go back to the Trace Dependent sheet.
• Select cell B5. We will check if any cell value is dependent on this cell.
• Go to the Formulas tab of the ribbon.
• In the Formula Auditing group, select Trace Dependents.

• If the cell is an active cell, you will see a dotted black line with an arrow pointing towards an icon of a table.
• This indicates the cell is an active cell and its dependent cell is in another worksheet.

• Double-click on the dotted line.

• You will see the Go To dialog box. It will show the sheet and the formula in which the active cell is used.
• Select the reference and click on OK.

• This will take you to the sheet where this formula is used. It will indicate the dependent cell whose value is dependent on the active cell.
• In our example, the result of cell D5 of sheet Trace Dependent 1 is dependent on the active cell B5 of the sheet Trace Dependent.

Method 2 – Applying VBA Code to Trace Dependents Across Sheets in Excel

Steps:

• Take two sheets and make the data set on both of the sheets like in the previous methods.

• Fill in the cells of column D of the data set in sheet VBA 1 by applying the formula, just like the previous method.

• Select cell B5 of sheet VBA.
• Go to the Developer tab of the ribbon.
• Choose Visual Basic.

• You will see the VBA window. From the Insert tab, choose Module.

• Copy the following code and paste it into the module:

Sub Trace_Dependents_Across_Sheets()
'Adding commands to show dependents
Selection.ShowDependents
'The arrow doesn't show any precedent
ActiveCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, _
LinkNumber:=1
End Sub

Sub Trace_Dependents_Across_Sheets()

Selection.ShowDependents
'The arrow doesn't show any precedent
ActiveCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, _
LinkNumber:=1

• Save the code.
• Press the run button or F5 to play it.

• After running the code, it will directly take us to cell D5 of sheet VBA 1, indicating it is the dependent cell.

• If you go back to the VBA sheet you will see cell B5 is marked with the trace dependent arrow, indicating it as an active cell.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Related Articles

• How to Trace Errors in Excel
• Trace Precedents and Dependents in Excel

<< Go Back to Trace Formula | Auditing Formulas | Excel Formulas | Learn Excel