Can you collate Ancestors based on another column condition?

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.

Find more posts tagged with

Formulas

Join

collect

parent row

Accepted answers

Maggie Rowland

The right prompts into Smartsheet Formula Generator got us there!

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

All comments

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), " - ")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.