Trace Dependents not showing dependents on different sheet

Question

I am using Excel for Mac v16.45

I am attempting to ensure that a number of cells are used in exactly one other formula. I would have expected to be able to select a range of cells and then ‘Trace Dependents’, then for each cell (with a dependent on another sheet in the same workbook) to be able to click on the icon/arrow/dashed line to see a dialog box with a list of (hopefully one) cell.

There are two problems.

The first is merely annoying and has a workaround - the Trace Dependents command only works on the first cell, no others in the range.

The second is extremely frustrating. No matter where I click (apart from one attempt out of possibly 100) I cannot get the mouse click to be detected in the icon/arrow/dashed line - it always detects the click as if the icon/arrow/dashed line is not there and selects the cell behind.

Answer 1

Hi Chris,

Based on your description, we tested in the same version of Excel for Mac. And we can reproduce this behavior.

If I select all ranges of this sheet, then click TraceDependents button, only the arrow from cell A1 will appear.  

However, if I select some specific range which has formula, the other arrow will appear to show the relationship. Moreover, if I select all ranges and then click Trace Dependents button at this time, all the arrows will appear.

We also test the same behavior in Excel for Windows. No matter what, only the relationships from A1 will appear when we click Trace Dependents button.

Based onthis article, it should show the relationship between the cells within the range you select. We appreciate your efforts on this case, and we are sorry for the confusion caused by this. We sincerely suggest you submit the feedback within Excel to send this issue report directly to the related Excel team. This is the best way to report issue and get feedback from them.

Your cooperation and understanding are highly appreciated.

Best Regards,

Ivy

Answer 2

The second is extremely frustrating. No matter where I click (apart from one attempt out of possibly 100) I cannot get the mouse click to be detected in the icon/arrow/dashed line - it always detects the click as if the icon/arrow/dashed line is not there and selects the cell behind.

The main ingredient is to click when the pointer looks like ↖︎ by placing it anywhere on the Precedent/Dependent arrow. That will produce the Go To dialog listing the pertinent references for those on a different sheet.

Answer 3

When a cell has a dependent cell and you use Trace Dependents, Excel puts an arrow to each dependent cell, and goes one layer deeper with each click of Trace Dependents.

If a dependent cell is on another worksheet, a black arrow appears pointing to a little icon. Click the black arrow to display the Go To Dialog. In the Go To dialog select a dependent cell and then click' the OK button to select that dependent cell. If the dependent cell is on a hidden worksheet, you must unhide the worksheet before you can select the dependent cell.

To go to dependent cells on the same worksheet, click the blue arrows.

Answer 4

Thanks for the response, as you can see from my question, I understand what is meant to happen.

Is your screen shot from Excel for Mac ? 

As you can see from my screenshot the arrow is covered by the icon. No matter how large I make my screen I can not get the pointer to be detected as in the area of pointer that is visible. I have increased the size of the cells to see if that positions that sheet icon differently but it does not move it. 

Weird design that the icon is not active too.

Answer 5

Thanks for letting me know. As you can see from my screenshot, I cant click the arrow component, it is hidden by the icon which is (for some reason) not active.