Parent/grand-parent rows in reports
Hello! I have tried to follow steps in other posts, but my brain cannot figure it out :) Could anyone help me identify the helper formulas for pulling parent cells into a report?
I need to pull in to a report the Goals and Problem Statements for any Projects that are identified as "Must Have". So I am trying to figure out the formulas I would need in the columns Helper 1 and Helper 2 to identify a parent that has any children with "must have" projects.
I am having trouble figuring out how to nest formulas for accomplishing this.
Thank you!
Answers
-
Hey @Kristen Olson
To make sure I understand your screenshot - you have child rows that are must-have projects. You want the grandparent (not the parent) of that row in a report.
In a checkbox helper column, we will identify parent rows that have Descendants with Must Haves.
=IF(AND(COUNT(CHILDREN([2021 Strategy]@row)) > 0, COUNTIFS(DESCENDANTS([Must Have]@row), 1) > 0), 1)
In a Text/Number helper column, (I often call this helper 'Level') the hierarchical level is outlined.
=COUNT(ANCESTORS([2021 Strategy]@row))
If the rest of your sheet follows the structure of your screenshot, you should be able to use the report builder to filter the data, choosing the Helper checkbox column when checked, and selecting this Helper Level column of the appropriate Level(s) (it looks like levels greater than 1 )
Will that work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!