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