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

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 07/01/24 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 ,

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 07/01/24

    = 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 | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    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 | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 07/01/24 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 ,

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • sbrittin2
    sbrittin2 ✭✭✭

    It looks like that did the trick! Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!