Ask Your Question
0

Show all cells referenced somewhere else

asked 2017-03-06 13:25:22 +0200

Libre comme un oiseau gravatar image

updated 2017-03-06 13:26:35 +0200

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-03-06 15:31:31 +0200

Lupp gravatar image

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!

edit flag offensive delete link more

Comments

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).

Libre comme un oiseau gravatar imageLibre comme un oiseau ( 2017-03-06 15:38:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-03-06 13:25:22 +0200

Seen: 106 times

Last updated: Mar 06 '17