1

The shortcut Ctrl+], which is used to highlight the dependent cells is not working across different worksheets...though the shortcut works pretty much fine in a single worksheet (where both the cell and the formula that uses the cell are situated).. However the precedent shortcut Ctrl+[ is working fine across multiple worksheets in a workbook..

Also that when we press "Ctrl+Shift+[" or "Ctrl+Shift+]" all the precedent cells or the dependent cells as the case may be gets highlighted within the same worksheet...but the same doesn't work across the different worksheets...

The shortcuts to highlight the precedent and dependent cells are very much handful things to identify various cell references (by colour formatting them) that are sitting across multiple worksheets..

So guys please someone help me to fix the issue..

Thankyou..

Improve this question

2 Answers 2

Reset to default
0

That shortcut doesn't always work. You are using Excel 2007 but it's the same in my current (365) version of Excel. For example, see this quote at Excel University:

If you would like to navigate to a precedent, use the Ctrl+[ keyboard shortcut. Generally speaking, when the active cell contains a formula, pressing Ctrl+[ will select the precedent cell, even if the precedent cell is on another worksheet. [. . .] For the most part, this shortcut works very well. Sometimes, when a formula is complex, or contains many precedents from different sheets, pressing it may not navigate to the desired sheet location. But for the most part, this is a great shortcut, especially when the the formula is simple.

Instead, since your aim is to identify rather than necesssarily navigate to the cells in question, try Trace Precedents and Trace Dependents on the Formlas ribbon tab:

enter image description here

These commands display arrows connecting the relevant cells, or an icon when a cell is on another sheet.

enter image description here

If you want to navigate to a precedent or dependent cell, you can do that from here too, by double-clicking the arrow-head. If the cell is on another sheet, Excel prompts you first with a dialog showing the cell reference:

enter image description here

Improve this answer
1
  • Thanks for your kind reply :) When "Go To" dialog box appears we are able to navigate to various precedent cells that were sitting across sheets... But what will be the case when there exist some (say) 20 to 30 precedent cells across sheets for each of (say) 30 cells sitting in the current work sheet??..it is quite tough to navigate to each of the cell and then highlighting them... So is there any way to highlight all of the precedent cells so that the work can be done quite easily??.. I see "Precedents" option in "Go To Special" but was not doing my work.. Thank you..
    – Hemanth
    Commented Jul 18, 2020 at 9:41
0

In the absence of a reliable way within Excel to highlight the precedent cells, since the shortcut you want is known to be unreliable, you can instead use the Show Formulas function.

enter image description here enter image description here

Although Show Formulas may highlight more cells than you need, it does at least provide a quick way to reveal which sheets the immediate precedents can be found on.

enter image description here

Improve this answer
1
  • Thanks for your reply... But could you also please provide some more easy approach... Finding the precedent and dependent cells across the sheets (with the shortcut) does the work more effectively... Isn't there anything to make the shortcuts work...
    – Hemanth
    Commented Jul 23, 2020 at 16:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .