Consolidating Parent/Children Rows in Reports

Kathy Tilley
Kathy Tilley ✭✭✭
edited 10/18/23 in Formulas and Functions

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

Tags:

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!