Is there a way to show all cells on a spreadsheet that have been referenced somewhere else?
For instance using a function in the Menu, all cells that have been referenced somewhere are shown in orange…
Thanks
Would you mind to tell what you want to achieve finally using such a tool? Probably there are better passable ways.
You surely know ‘Tools’ > ‘Detective’. It is not what you actually want, but I’m afraid tracing precedents/dependents for one cell at a time is the only way to at least come a bit closer to what you want to achieve.
To test for a specific cell, say B5, if it is referenced as a single cell (not inner part of a cellrange) within a certain range, say J1:M15, you may use the formula
{=SUMPRODUCT(ISNUMBER(SEARCH("(^|[^A-Z]+)"&SUBSTITUTE(CELL("address";B5);"$";"")&"([^0-9]+|$)";SUBSTITUTE(FORMULA(J1:M15);"$";""))))}
enterd for array evaluation with RegEx in formulae enabled.
This is not a joke, and in fact I suppose there is no significantly simpler formula to perform the test. (With older versions even this one may not work, because there was a bug concerning the FORMULA function under array valuation if I remember correctly.)
Your colouring aim would require to use even more complicated formulae in a condition for Conditional Formatting and to get them work with array evaluation. Litterally taken these formula woul have to check upt to 2^30 cells and also be appliedcfor 2^30 cells. Not a realistic idea!
To answer your question: have a spreadsheet that gathers all things paid last year in groups of numbers. Each group is summed in a different cell, then each of these sums is used in another sheet. I want to make sure all these sums are actually referenced somewhere else (the “another sheet”) and not left alone (rather than check manually).