Menu
I was searching regarding highlighting cells from other sheet (in edit mode) but could not find any solution to it. When a cell is selected and in edit mode, the cells used in the formula gets highlighted if they are from same sheet. For example (see following image) cell K6 is selected from sheet "data" and cells used in the formula are highlighted with red, blue and purple color rectangles.
But when formula contains the cell from other sheet it will not be highlighted. For example (see following image) cell D5 from sheet"Chart" is selected. The cell uses X6 cell from sheet "data" but it is not highlighted when we are editing formula in cell D5 from sheet "Chart". It will be very helpful to debug the sheets if it was possible to highlight cells from other sheet while editing the formulas in cell. Is there any way to do this?
963
On the Home tab, click Conditional Formatting, point to Highlight Cells Rules, and then click Text that Contains. In the box next to containing, type the text that you want to highlight, and then click OK.
You can use Spreadsheet Inquire in excel-2013 and excel-2016.
It can go beyond the direct dependents and precedents of that cell, and show the cells that underpin the direct cells etc.
159
A work around:
1. click the cell with the formula you want to trace
2. go to the Formulas menu (in the top menu)
3. click on "Trace Dependents" under "Formula Auditing"
4. you will see blue arrows from referenced cells in the same sheet, and a black dashed arrow to a little drawing representing other sheets
5. double click the dashed line, the "Go to" window pops up
6. select a reference in the list
7. click OK, it will take you to the referenced cell in the other sheet
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
