Can you collate Ancestors based on another column condition?

Maggie Rowland

Hi all, looking for some formula help.

I would like to collate the names of my ancestors for a task in my "Stage" column, but only if the Hierarchy level of that parent or grandparent is >= 3.

I have tried utilizing the Join(Ancestors(Task@row), " - ") and this returns all of the parent tasks. When I try to utilize an if statement, I get "UNPARSEABLE" or "INCORRECT ARGUMENT SET."

Any help is appreciated.

Maggie Rowland

The right prompts into Smartsheet Formula Generator got us there!

=JOIN(COLLECT(ANCESTORS(Task@row ), ANCESTORS(Hierarchy@row ), >=3), " - ")

Darren Mullen

@Maggie Rowland Use join() wrapped around a collect() . the collect() will let you collect data from the ancestors() range based on the criteria from other column(s)

Maggie Rowland

Thanks for the direction - gave that a shot

=JOIN(COLLECT(ANCESTORS(Task@row ), Hierarchy:Hierarchy >= 3), " - ")

Maggie Rowland

The right prompts into Smartsheet Formula Generator got us there!

=JOIN(COLLECT(ANCESTORS(Task@row ), ANCESTORS(Hierarchy@row ), >=3), " - ")