Consolidating Parent/Children Rows in Reports
Good Afternoon - I've recently been assigned the Ops Planning Project from another PM mid-way through the process. We have 6 company objectives that each of our 13 department's objectives must roll up under. Each department has completed a Smartsheet with their individual ops plan using the following hierarchy:
- Parent Row - Company Objective #1 (this is identical on all the individual Ops Plan sheets)
- Child Row -Department Objective #1
- Child of Child Row - Activity supporting Objective #1
- Child of Child Row - Activity supporting Objective #1
- Child Row -Department Objective #2
- Child of Child Row - Activity supporting Objective #2
- Child of Child Row - Activity supporting Objective #2
- Child Row -Department Objective #1
- Parent - Company Objective #2 (this is identical on all the individual Ops Plan sheets)
- Child - Department Objective #1
- Child of Child - Activity supporting Objective #1
- Child of Child - Activity supporting Objective #1
- Child - Department Objective #1
- Etc…..for each of the 6 Company Objectives.
I now need to be able to pull a report consolidating the team objectives by Company Objective i.e.
- Company Objective #1
- Department Objective (including identifying which department)
- Activities
- Department Objective
- Activities
- Department Objective
- Activities
- Department Objective
- Activities
- Company Objective #2
- Department Objective (including identifying which department)
- Activities
- Department Objective
- Activities
- Department Objective
- Activities
- Department Objective
- Activities
- Department Objective (including identifying which department)
- Department Objective (including identifying which department)
I'm thinking I'll need to add some helper columns in the individual Ops Plan sheets to consolidate the data but not sure where to start.
Appreciate any help/direction anyone can provide.
Thanking you in advance.
Kathy Tilley
Best Answer
-
You will need to add a [Company Objective] column as well as a [Department Objective] column to each of the source sheets.
[Company Objective] column formula:
=IFERROR(INDEX(ANCESTORS([Column Name]@row), 1), [Column Name]@row)
[Department Objective] column formula:
=IF(COUNT(CHILDREN([Column Name]@row)) = 0, PARENT([Column Name]@row))
Then you would create your report and first group by [Company Objective] then further group by [Department Objective].
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
You will need to add a [Company Objective] column as well as a [Department Objective] column to each of the source sheets.
[Company Objective] column formula:
=IFERROR(INDEX(ANCESTORS([Column Name]@row), 1), [Column Name]@row)
[Department Objective] column formula:
=IF(COUNT(CHILDREN([Column Name]@row)) = 0, PARENT([Column Name]@row))
Then you would create your report and first group by [Company Objective] then further group by [Department Objective].
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul - thank you so much for your quick response!
I followed your instructions exactly and my report now looks great! I'm really good with copy and paste but not so good at formulas ☹️. I understand what you've done in the formula for the Department Objective (if the number of children rows = 0, put in the parent row) but would you mind explaining the Company Objective formula?
Thank you again for your help! I very much appreciate it!
Warmly,
Kathy Tilley
-
Happy to help. 👍️
[Company Objective]:
=IFERROR(INDEX(ANCESTORS([Column Name]@row), 1), [Column Name]@row)
Create an array of all ancestor rows.
=IFERROR(INDEX(ANCESTORS([Column Name]@row), 1), [Column Name]@row)
Output the first ancestor.
=IFERROR(INDEX(ANCESTORS([Column Name]@row), 1), [Column Name]@row)
Since it does not have an ancestor, it will throw an error.
The only rows that will throw this error / do not have an ancestor row are the rows we are pulling in.
Using those two pieces of information, we use an IFERROR to say if there is an error, grab [Column Name]@row.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Ahhh! Thanks Paul. Now I get it 😀
Appreciate your help! The Senior Leadership Team is loving their new reports!
Have a wonderful day!
Warmly,
Kathy Tilley
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!