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

Many times, in Excel, users use formulas to show desired values. The results from this formula depend on other cell values on that particular sheet or on another sheet in the same workbook. Our main goal is to show the dependency of a cell’s value on other cells in another worksheet. In this article, we will show you how to trace dependents across sheets in Excel.

Download Practice Workbook

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

Trace Dependents

We can define trace dependents as a single cell or a range of cells that affect the value of other cells. The dependent cell depends on the values of the active cells to show the result. For example, cell B8 contains the formula =B6-B7. Here, cells B6 and B7 are active cells because the value of cell B8 depends on both B6 and B7, and they are the trace dependents.

2 Easy Ways to Trace Dependents Across Sheets in Excel

In this article, you will see two different ways to trace dependents across sheets in Excel. In our first method, we will use the Trace Dependents command of Excel to show the dependents. For our second procedure, we will apply a VBA code for the same purpose.

To illustrate our article, we will use the following data set. Here in columns B and C, we have some order ids and their corresponding products, respectively.

1. Using Trace Dependents Command to Trace Dependents Across Sheets

For our first procedure, we will use the Trace Dependents command, which is located on the Formulas tab of the ribbon. By selecting this command, we can see the active cells and the dependent cells of a particular formula or value. The detailed steps for this procedure are as follows.

Step 1:

• First of all, we will take two worksheets to make a data set.
• As we will show trace dependent across sheets, we will need at least two worksheets.
• In the following image, we will make the data set in the Trace Dependent sheet.

Step 2:

• Secondly, we will take another worksheet and name it Trace Dependent 1.
• Also, we will make an extra column to apply a formula that will contain the cell addresses from both sheets.
• Then, write the following formula of the COUNTIF function in cell D5.

Step 3:

• Thirdly press Enter to see the result.
• Then, with the help of the AutoFill feature, we will show the results for the lower cells as well.

Step 4:

• Fourthly, go back to the Trace Dependent sheet.
• Then, select cell B5.
• Here, we will check if any cell value is dependent on this cell.
• Then, after selecting the cell go to the Formulas tab of the ribbon.
• From there, in the Formula Auditing group, select Trace Dependents.

Step 5:

• Fifthly, if the cell is an active cell you will see a dotted black line with an arrow pointing towards an image.
• This indicates, the cell is an active cell and its dependent cell is in another worksheet.

Step 6:

• Then, keep the mouse on the dotted line’s end and double click on it.

Step 7:

• In this step, you will see the Go To dialog box after double-clicking.
• Consequently, the box will show the sheet and the formula in which the active cell is used.
• Then select the reference and click on OK.

Step 8:

• Finally, the action from the previous step will take you to the sheet where this formula is used.
• Also, 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.

Read More: How to Trace Formula in Excel (3 Effective Ways)

2. Applying VBA Code to Trace Dependents Across Sheets in Excel

As our second method, we will apply a VBA code to trace dependents across sheets in Excel. We will give the correct sequence and command in the code, and this will show the dependents and the active cell. For a better understanding, follow the following steps.

Step 1:

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

Step 2:

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

Step 3:

• Thirdly, we will apply the code to trace dependents.
• For that, select cell B5 of sheet VBA.
• Then, go to the Developer tab of the ribbon.
• From there, choose Visual Basic.

Step 4:

• Fourthly, you will see the VBA window.
• Here, from the Insert tab choose Module.

Step 5:

• Fifthly, 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

Step 6:

• Then, save the code after pasting it.
• After that, keep the cursor on the module and press the run button or F5 to play it.

Step 7:

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

Step 8:

• Consequently, 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.

Read More: How to Trace Dependents to Another Sheet in Excel

Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to trace dependents in Excel  across sheets by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.

The Exceldemy team is always concerned about your preferences. Moreover, you need to keep in mind that the submitted comments need to be approved. Therefore, after commenting, be patient and we will reply to your queries as soon as possible.

Related Articles

• How to Trace Errors in Excel (with Quick Steps)
• Trace Precedents and Dependents in Excel (with Quick Steps)