Is there any way to tell which formulas a particular cell is in, reverse look-up style? That is, is there a way to search somehow for which formulas in a sheet contain a particular cell in them without opening all of them up? If not, it would be nice

Question

Is there any way to tell which formulas a particular cell is in, reverse look-up style? That is, is there a way to search somehow for which formulas in a sheet contain a particular cell in them without opening all of them up? If not, it would be nice

Is there any way to tell which formulas a particular cell is in, reverse look-up style? That is, is there a way to search somehow for which formulas in a sheet contain a particular cell in them without opening all of them up? If not, it would be nice.

I'm fixing a Numbers document that I use in business daily that's become overly complicated, but while I want to clear/delete a number of cells in it to clean it up, I'm not sure if those cells are being used as source for other formulas on the page. I don't have to open all my formulas one by one to see if that cell is in there, do I?! That'd be a huge amount of work if I wanted to eliminate say 10 cells to search each formula on the page to see if it is in there or watch all the cells when I delete that cell to see if the numbers change! There's got to be an easier way! Right click doesn't seem to be any help.

I don't want to damage my daily tool to make it cleaner and reorganize it. Is there really no way to do a reverse look-up of which formulas a particular cell is in?! Any time I click on a formula it highlights all the CELLS in that formula. Why can't I (or can I?) do the reverse and see all the FORMULAS that contain a particular cell?

As a Numbers document ages or gets used frequently, you can sometimes forget which cells are tied to which formulas. You just look for the results of your formulas. Would be a nice thing to be able to do. Thoughts? Work-arounds? Answers?

MacBook Pro (Retina, 15-inch, Mid 2014), OS X Yosemite (10.10.2), null

Posted on Feb 26, 2015 7:54 PM

Reply

Answer 1

Hiroto

Level 5

7,461 points

Feb 27, 2015 1:58 AM in response to andintroducing

Answer would be no unless application provided such feature. Numbers does not as far as I know.

Note that even if you have complete list of formulae used in a table, identifying formulae referencing any given cell is not simple.

E.g., cell T20 is referenced not only in

=T20+1

but also in

=SUM(T) =SUM(20:20) =SUM(S19:U22) =OFFSET(S1,19,1,1,1) =INDIRECT("T"&(10*2))

In order to identify such formula, we have to actually evaluate it to see if it references the given cell. Especially such formula as OFFSET() and INDIRECT() may not be found by static parser because their parameters can be dynamic.

Sorry to be the bearer of bad tidings.

H

Reply