Strategy: The trick only works if you turn off the “in-cell editing” feature. As shown in Fig. 479, this is on Tools – Options – Edit, the top item on the left side.
1) Turn off the Edit Directly in Cell option. You can then go to any cell
with a formula, as shown in Fig. 480.
2) Double-click the cell with the mouse. Excel moves to the first cell in the original formula. In this case, as shown in Fig. 481, that is cell E30, just a couple of rows up.
3) Hit the Enter key, and Excel will navigate to the next cell in the original formula. This will be cell C4, all the way at the top of the worksheet. Excel scrolls so that you can see cell C4. From this view, you can see that Excel has actually selected all of the precedent cells, as shown in Fig. 482.
4) Continue hitting Enter to continue cycling through the precedents.
Gotcha: This feature was added to Excel back in Excel version 4. This was the last Excel version with only one worksheet in a workbook. They never dreamt of supporting precedents on other worksheets. So – this technique will not navigate to precedents on other sheets.
Shameless Commercial: When Howard pointed out this feature, he asked what it would take to actually make it work for any and all sheets. MrExcel Consulting wrote a utility for Howard that does a great job of tracing precedents, as shown in Fig. 483.
It lists all of the precedents, even precedents on other worksheets or in other open workbooks. You can use Next/Prev to move that particular precedent to the middle of the screen. If you are someone who needs to be able to track precedents, write to MrExcel Consulting to buy this utility.
Summary: There is an obscure feature in Excel that lets you navigate to all precedents of a formula. It works fairly well for precedents on the current worksheet.
Commands Discussed: Tools – Options – Edit – Edit In Cell
No comments:
Post a Comment