Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

New Hierarchy Function - ANCESTORS()

KrisWalsh ✭✭✭✭✭
edited 12/09/19 in Archived 2016 Posts



I got super excieted when I saw the new Hierarchy functions, ANCESTORS() & PARENT().


I get PARENT(), know where I plan to use it and am happy to have it.


After testing out ANCESTORS() and reading the blog, I realize that it's primary intention is for forconditional formatting. ANCESTORS() Example Sheet


I'm curious to know if anyone has other use cases for ANCESTORS. 



  • Ancestors can create conditional formatting with a column of =count(ancestors()), but it's useful in other contexts too.  The trick is that Ancestors returns a collection of things (similar to a range), so you have to tell it how to turn that collection into a single value.  You could create a breadcrumb of all of the ancestors of a given cell with =join(ancestors(), " > "), which would return "Grandparent > Parent".  Or you could grab one item out of the ancestors collection by position in the collection - e.g. getting the first ancestor with =index(ancestors(), 1).  Either of these might be useful in reports, where hierarchy is flattened and it can be hard to distinguish similar items. 




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Not yet, but I'm still thinking about it.


    The number is not fixed because the level of indentation is not fixed.

    As a the last child in the line, I could be 1 or 7, depending on how many ancestors I have.



This discussion has been closed.