Consolidating Parent/Children Rows in Reports

Kathy Tilley

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:

1. Parent Row - Company Objective #1 (this is identical on all the individual Ops Plan sheets)
   1. Child Row -Department Objective #1
      1. Child of Child Row - Activity supporting Objective #1
      2. Child of Child Row - Activity supporting Objective #1
   2. Child Row -Department Objective #2
      1. Child of Child Row - Activity supporting Objective #2
      2. Child of Child Row - Activity supporting Objective #2
2. Parent - Company Objective #2 (this is identical on all the individual Ops Plan sheets)
   1. Child - Department Objective #1
      1. Child of Child - Activity supporting Objective #1
      2. Child of Child - Activity supporting Objective #1
3. 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

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

Paul Newcome

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].

Paul Newcome

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].

Kathy Tilley

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

Paul Newcome

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.

Kathy Tilley

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

Paul Newcome

Happy to help. 👍️