Building an if statement off a specific ancestor

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.

Screen Shot 2021-02-24 at 4.12.29 PM.png

Thanks in advance for your help!

Find more posts tagged with

Accepted answers

All comments

Kelly Moore

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

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.

Screen Shot 2021-02-26 at 4.22.13 PM.png

Kelly Moore

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?

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.