Stuart Field

Q: Can I search for cells that contain a reference to a given cell?

I want to find which cells contain a reference to a given cell. This would aid debugging of spreadsheets considerably. Can this be done?

Posted on Apr 17, 2016 3:41 PM

Close

Q: Can I search for cells that contain a reference to a given cell?

  • All replies
  • Helpful answers

  • by SGIII,

    SGIII SGIII Apr 17, 2016 4:57 PM in response to Stuart Field
    Level 6 (10,637 points)
    Mac OS X
    Apr 17, 2016 4:57 PM in response to Stuart Field

    References only from formulas within the same table? Or references from formulas in cells anywhere in the document? Do you want the address of the cell containing the reference?  Or do you want the cell to be highlighted? (This should be possible via a short AppleScript, but more details needed).

     

    SG

  • by Hiroto,Solvedanswer

    Hiroto Hiroto Apr 17, 2016 5:18 PM in response to Stuart Field
    Level 5 (7,281 points)
    Apr 17, 2016 5:18 PM in response to Stuart Field
  • by SGIII,

    SGIII SGIII Apr 17, 2016 5:31 PM in response to Stuart Field
    Level 6 (10,637 points)
    Mac OS X
    Apr 17, 2016 5:31 PM in response to Stuart Field

    Wow, so little faith! Maybe this couldn't be done in older versions of Numbers.  But useful things along these lines ARE generally possible in the current version of Numbers. For example, the short script below will highlight all cells in a table with formulas that directly reference a selected cell.

     

    Just copy-paste into Script Editor (in Applications > Utilities) , click in a Numbers cell, and run the script.

     

    Screen Shot 2016-04-17 at 8.17.48 PM.png

     

    Capturing all references to the cell would get complicated (for example this doesn't highlight the SUM formula at the bottom).  But this still useful for many debugging tasks, as a bug is most likely to be in a specific reference rather than a column sum.  The script easily extended to other tables in the document.

     

    This won't work in the seven year old version of Numbers that I think H is still using.  But it's really easy to use in the current version.

     

    It's also easy to get a script to produce a list of formulas used in a document, to aid in debugging.

     

    SG

     

    tell application "Numbers"

      tell front document to tell active sheet

      tell (first table whose selection range's class is range)

      set refdCell to (get selection range)'s cell 1's name

      repeat with c in cells

      tell c to if its formula contains refdCell then ¬

      set its background color to "red"

      end repeat

      end tell

      end tell

    end tell


  • by Barry,

    Barry Barry Apr 17, 2016 5:39 PM in response to Stuart Field
    Level 7 (32,271 points)
    Apr 17, 2016 5:39 PM in response to Stuart Field

    Hi Stuart,

     

    This isn't meant as a particularly serious suggestion, but one way to indicate every cell in a document referencing, or otherwise depending on the contents of a particular cell is to replace that cell's current contents with something that will produce an error. That will throw up an error flag in the cell, and in every other cell whose value depends on that cell.

     

    =12/0 would do it. Reverting to the original content of the cell should change everything back, provided you've done no editing in the affected cells.

     

    If you try this, I'd recommend using a COPY of your file, with a different file name, rather than the original. Tested, but NOT extensively tested.

     

    Regards,

    Barry

  • by SGIII,

    SGIII SGIII Apr 17, 2016 5:50 PM in response to Stuart Field
    Level 6 (10,637 points)
    Mac OS X
    Apr 17, 2016 5:50 PM in response to Stuart Field

    The result of Barry's suggestion in my example:

     

    Screen Shot 2016-04-17 at 8.42.26 PM.png

     

    The script captures "too little," i.e. misses A6.

     

    Introducing an error captures "too much,"  i.e. includes C6.

     

    In any case, getting a quick read on cells that reference a given cell generally speaking IS possible in Numbers, despite conclusions in other threads. Hope you haven't given up on that idea!

     

    SG

  • by Barry,

    Barry Barry Apr 18, 2016 1:42 AM in response to SGIII
    Level 7 (32,271 points)
    Apr 18, 2016 1:42 AM in response to SGIII

    "Introducing an error captures "too much,"  i.e. includes C6."

    As it should.

    As described, it "indicate(s) every cell in a document referencing, or otherwise depending on the contents of a particular cell"

     

    Regards,

    Barry

  • by SGIII,

    SGIII SGIII Apr 18, 2016 7:50 AM in response to Stuart Field
    Level 6 (10,637 points)
    Mac OS X
    Apr 18, 2016 7:50 AM in response to Stuart Field

    Barry's "error" approach is really easy to use and will often do the trick. In many cases, however, debugging one level at a time can be more helpful than showing even those cells that are indirectly affected.

     

    Here is what 'Trace Dependents' shows in Excel:

     

    Screen Shot 2016-04-18 at 10.47.20 AM.png

     

     

    A close equivalent in Numbers can easily be achieved by simply expanding the script a little to capture more cases, resulting in this:

     

    Screen Shot 2016-04-18 at 10.34.12 AM.png

     

     

    Trying either–or a combination of–the script and introduce error approaches is surprisingly straightforward and more useful than mistakenly assuming this isn't easily done in Numbers.

     

    SG

     

     

     

    tell application "Numbers"

      tell front document to tell active sheet

      tell (first table whose selection range's class is range)

      set refdCell to (get selection range)'s cell 1's name

      set colLtr to refdCell's text item 1 -- assumes not using really "wide" table

      repeat with c in cells

      tell c

      if refdCell is in its formula ¬

      or "(" & colLtr is in its formula or ¬

      colLtr & ")" is in its formula then ¬

      set its background color to "red"

      end tell

      end repeat

      end tell

      end tell

    end tell

  • by Hiroto,

    Hiroto Hiroto Apr 18, 2016 3:09 PM in response to SGIII
    Level 5 (7,281 points)
    Apr 18, 2016 3:09 PM in response to SGIII

    I can't be bothered with this sort of noise.

     

    Suffice it to say that your miserable scripts do not even try to address any of the last 4 examples I gave in the referenced thread.

     

    Why you can arbitrarily trivialise the problem to such an extent and become ludicrously triumphant over a straw man is totally beyond me.

     

    H

     

     

    EDIT : changed 'last 3' to 'last 4'.

  • by SGIII,

    SGIII SGIII Apr 18, 2016 4:50 PM in response to SGIII
    Level 6 (10,637 points)
    Mac OS X
    Apr 18, 2016 4:50 PM in response to SGIII

    It's a fair comment that the scripts don't handle some of the examples given in the other thread, just very common cases.  Which may not be such a bad thing.  Here is how Excel's built-in 'Trace Dependents' handles some of the examples given in the other thread:

     

    Screen Shot 2016-04-18 at 7.20.15 PM.png

     

    It spots two direct dependents, but (like the simple scripts) it doesn't try to "see" OFFSET and INDIRECT dependents. It is nonetheless a highly useful feature for debugging.  As are the simple scripts.

     

    The scripts aren't perfect, but they're short and easy to use with Numbers 3.  Scripting this kind of thing in the old Numbers 2, if possible, was much tougher because it wasn't easy to access formulas. AppleScript support has improved in important ways in Numbers 3, making some things practical that weren't really worth trying before.

     

    The simple script approach combined with Barry's nifty "introduce an error" approach cover pretty much any likely case for debugging a chain of calculations, including (I believe) all the common and not so common cases mentioned in the other thread.  The nice thing is that these approaches really are easy to apply.  I'm happy to add them both to my bag of Numbers tricks.

     

    SG

  • by SGIII,

    SGIII SGIII Apr 19, 2016 2:19 PM in response to Stuart Field
    Level 6 (10,637 points)
    Mac OS X
    Apr 19, 2016 2:19 PM in response to Stuart Field

    And here is a slightly more sophisticated script that's closer to Excel's 'Trace Dependents':

     

    Screen Shot 2016-04-19 at 5.05.19 PM.png

     

    Usage exactly the same.  Clicked in cell A3 and then clicked the "Run" button.

     

    Clicking cell B3 and "Run" gives this:

     

    Screen Shot 2016-04-19 at 5.18.02 PM.png

     

    Numbers 3 only.

     

    SG

     

     

     

    tell application "Numbers"

      tell front document to tell active sheet

      set t to (first table whose selection range's class is range)

      tell t

      set tCell to (get selection range)'s cell 1's name

      set colLtr to tCell's text item 1

      repeat with c in cells

      tell c

      set f to formula

      if f is not missing value then -- common cases first

      set dRef to tCell is in f ¬

      or "(" & colLtr is in f ¬

      or colLtr & ")" is in f

      if dRef is false then -- dependents via range refs

      set rRef to false

      repeat with i in my splitFormula(f)

      if i contains ":" then -- we have a range

      tell t to set theRange to range i

      repeat with d in theRange's cells

      if d's name is tCell then

      set rRef to true

      exit repeat

      end if

      if rRef then exit repeat

      end repeat

      if rRef then exit repeat

      end if

      end repeat

      end if

      if dRef or rRef then set background color to "red"

      end if

      end tell

      end repeat

      end tell

      end tell

    end tell

     

    to splitFormula(f) -- lists components of a formula so ranges can be extracted

      set text item delimiters to {",", "(", ")", ";"}

      return f's text items

      set text item delimiters to ""

    end splitFormula