# 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:
`=COUNTIF('Trace Dependent'!B5:B10,'Trace Dependent 1'!B5)`

• 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()
Selection.ShowDependents
'The arrow doesn't show any precedent
ActiveCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=1, _
End Sub``````

VBA Breakdown

• Firstly, we are calling the Sub procedure Trace_Dependents_Across_Sheets.
``Sub Trace_Dependents_Across_Sheets()``
• Then, the following commands will show dependents and active cell.
• The number of arrow will be one and the arrow will not navigate towards precedent cell
``````Selection.ShowDependents
'The arrow doesn't show any precedent
ActiveCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=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.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF