Building an if statement off a specific ancestor

Options

We use smartsheet to manage our content planning across several channels and teams using parent/child as an organizational method. I'm trying to build a filter out to show only projects and tasks that fall under a specific top-level (level 0) project.

I'd like to build a formula (likely an if statement?) that returns the label "Campaign" for all items (child and grandchild tasks) that fall under a specific ancestor (in this case, All Campaign Work).

Doing so will allow us to filter our view to only show projects and tasks that fall under that ancestor.

I've attached a sample image of the sheet.


Thanks in advance for your help!

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Laura Mantzoros

    Try this in your [Filter Label] column

    =IF(COUNT(ANCESTORS()) = 0, "Campaign", PARENT())

    Once 'Campaign' is inserted in the top level row, "Campaign" is cascaded downward. If you think you will use the Ancestors level in more scenarios, consider building a helper column for the Ancestors and insert the =COUNT(ANCESTORS()) into

    cheers,

    Kelly

  • Laura Mantzoros
    edited 02/26/21
    Options

    Thanks @KDM !

    I'm still learning my way around smartsheet. How would I build in the helper column logic?

    For reference, I tried building out an if formula to reference the specific ancestor, but it returned unparseable.


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Laura Mantzoros

    I just stumbled on your second question. I am so sorry for the delay - I don't know how I didn't see this when it first came across. Are you still needing a solution?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!