New Hierarchy Function - ANCESTORS()

http://help.smartsheet.com/articles/775363-using-formulas

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.

Find more posts tagged with

Formulas

Comments

Erik Rucker

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.

Cheers,

Erik

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.

Craig