How to Trace Dependents to Another Sheet in Excel

If you are wondering if a cell of your worksheet is dependent on any other cells on other sheets and can’t figure out how to trace those dependents, you have come to the right place. In this article, I will show you how to trace dependents to another sheet in excel. So let’s begin our journey.

2 Useful Methods to Trace Dependents to Another Sheet in Excel

In this section, we will demonstrate 2 effective methods to trace dependents to another sheets in excel with appropriate illustrations. Hence, we take an example worksheet where in sheet 1, we have some students’ Names and Ages in a class.

On the other hand, on Sheet 2, we have the Average, Maximum and Minimum value of the age on the 1st sheet.

Now, we will trace the dependent cells on sheet 2 for a cell in sheet 1. Let’s try the first method.

1. Use Trace Dependents Command for Tracing Dependents on Another Sheet

Trace Dependents Command is a built-in feature that is very useful when it comes to tracing dependent cells on the same sheet or across different sheets. For ease of operation, we rename Sheet 1 as Trace Dependent 1 and Sheet 2 as Trace Dependent 2. Now, follow the steps below for tracing dependent cells.

Steps:

• Select any cell for which you want to trace dependent cells. In this case, cell C5 is selected.

• Now go to Formulas In the Formula Auditing Group, select Trace Dependents.

• Now you should see an arrow emerging from C5, pointing toward an icon like this below.

• Now, bring your mouse pointer to the pointing line and double-click on the mouse’s right button. You will see a Go To dialogue box.

• In the box, we can see that cell C5 has 3 dependent cells on the sheet Trace Dependent 2. They are C5, C6, and C7.
• Now, click on any of the three references to go to that reference, and then click OK.

• This will take you to your referenced dependent cell. As a result, we have landed on C5 of sheet Trace Dependent 2

• In this way, we can check dependent cells for any cells in a workbook, even if the dependent cells are on the other sheets.
• Now, try yourself for other cells.

2. Run a VBA Code for Tracing Dependents on Another Sheet

You can also apply a VBA code for tracing dependent cells. Before proceedings, let’s rename Sheet 1 and Sheet 2 as  VBA 1 and VBA 2, respectively, for our convenience. Now to run the Code, follow the steps below:

Steps:

• Press Alt+F11 for Windows (for Mac press Opt+F11 or Fn+Opt+F11) to open VB editor window. You can also go to the Developer Tab and Select the Visual Basic

• Now, on the Insert Tab, choose

• You should have a new window like this.

• Now, paste the VBA code below.

Code:

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

• Now select cell C5 on VBA 1 sheet and run the Code by clicking the Play button shown in the figure by pressing

• You will see that you have been taken to a dependent cell of C5. Here, in the figure below, the Code has brought us to cell C5 of the VBA 2

• Now go to VBA 1 You can see that the Tracing Arrow that we have seen in Method 1 has also appeared here. So by using the same procedure, here you can also see the total dependent cells and go to them accordingly.

Things to Remember

• You can remove arrows by clicking on the Remove Arrows Option in the Formula Auditing group.

• You can also use both methods for tracing dependent cells on the same sheet as well.

Conclusion

That’s the end of today’s article. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. We will do our best to answer as soon as possible. Finally, please visit Exeldemy for more exciting articles on Excel.