How to highlight (cells from other sheet) while editing formula?

14
votes

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. enter image description here

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? enter image description here

3
vbaexcelexcel-formula
I am interested if anyone has a workaround to your question, but I think the bigger issue is in my opinion - why is your data being pulled cell by cell? Wouldn't you rather pull it using INDEX/MATCH, VLOOKUP, pivot table, etc? You are practically begging for transposition errors by having each data point linked manually. – Grade 'Eh' Bacon
The formulas in the above images are for representative purpose only. I am working on a confidential project and I can't release screen-shots of that data or any formulas in it. There are lot of formulas which use multiple cells from other sheets (which can't be pulled using VLOOKUP or MATCH&INDEX), and it becomes really difficult to debug or check calculations, hence I thought if there is any way to highlight used cells from other sheets in edit mode. – Ethun_Hunt
Ah - I understand your situation completely (with no solution in mind, unfortunately). I think to really do this properly you'll need VBA or an add-in, as you can't pull the actual formulas out from a cell without VBA. Therefore you can't manipulate a cell to see if it references the one you want, meaning you'd need to rely on built-in formula auditing as answered below, which I understand is insufficient for your purposes. Honestly what I do in such situations is select the text of a formula in sheet 1 then drop it into a random cell into sheet 2, where the references are, and audit there. – Grade 'Eh' Bacon

3 Answers

7
votes

You can use Spreadsheet Inquire in and .

It can go beyond the direct dependents and precedents of that cell, and show the cells that underpin the direct cells etc.

enter image description here

1
votes

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

1
votes

There is an extension you can use that could potentially solve your problem. It's called FormulaDesk: https://www.formuladesk.com/

It has a feature called Formula Explorer. When you click on a cell reference from another sheet in the formula explorer, it will present you with the cell (range) value, cell address, and even a screenshot of the area from the sheet.

See this picture for clarification:

FormulaDesk

Hope this helps!