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!!
Best Answer
-
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 ,
Answers
-
= 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
-
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.
-
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 ,
-
It looks like that did the trick! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!