Trying to search and return text found in Ancestors

I am trying to search the ancestors for any given cell and return the content of the ancestor if it meets specific criteria.

For example, if the Ancestor has the word "Chapter," I would like it to return all of the content from that Ancestor cell.

I've started with this formula, and I know the PARENT part is what's wrong. Unfortunately, I don't know what to use instead. (I don't know how to pull an Ancestor like you can a Parent.)

=IF(CONTAINS("Chapter", ANCESTORS([Task Name]@row)), PARENT([Task Name]@row))

Any help would be much appreciated!!

Find more posts tagged with

Accepted answers

Brian_Richardson

Ok not sure why CONTAINS won't function in a MATCH, but instead of doing INDEX MATCH you can simply COLLECT the results that you're looking for, JOIN them, and place in a cell with this formula:

=JOIN(COLLECT(ANCESTORS([Task Name]@row), ANCESTORS([Task Name]@row), CONTAINS("Chapter", @cell)), ",")

COLLECT puts together an array of Ancestor values where the Task Name contains the word "Chapter"
Then JOIN takes the results and posts them into your cell, separated by a ,

All comments

Brian_Richardson

= INDEX(ANCESTORS([Task Name]@row), MATCH(CONTAINS("Chapter",@cell), ANCESTORS([Task name]@row), 0))

This works like this, from "inside" out:

ANCESTORS gives you a temporary list of the values of all the ancestors of your row
CONTAINS looks through that list of Ancestor values one at a time (the @cell part) to find one with "Chapter"
MATCH returns the position in the list of Ancestors where "Chapter" was found
INDEX returns the value from that list of Ancestor values, from the position that MATCH returned

Brian_Richardson

Hmm…strike that. I typed this out before trying it. I'm getting INVALID OPERATION due to the CONTAINS and I'm not sure why.

Brian_Richardson

Ok not sure why CONTAINS won't function in a MATCH, but instead of doing INDEX MATCH you can simply COLLECT the results that you're looking for, JOIN them, and place in a cell with this formula:

=JOIN(COLLECT(ANCESTORS([Task Name]@row), ANCESTORS([Task Name]@row), CONTAINS("Chapter", @cell)), ",")

COLLECT puts together an array of Ancestor values where the Task Name contains the word "Chapter"
Then JOIN takes the results and posts them into your cell, separated by a ,

sbrittin2

It looks like that did the trick! Thank you!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.