Health for Parent Row Based on Health of Child Rows

Options

Hello, I need some assistance combining the following formulas into a single Column Formula.

Formula 1:

  • Successfully automates Health for Tasks based on desired criteria.
  • Should only apply if the row is a Task.

Formula 2:

  • Successfully automates Health for Milestones & Project Components based on desired criteria.
  • Should only apply if the row is either a Milestone or a Project Component.

Problem:

  • The above formulas need to be combined into a single Column Formula in order to fully automate Health for Tasks, Milestones, and Project Components across the Project Schedule.

Notes:

  • The Project Schedule defines and formats Milestones and Project Components based on nesting and the use of Checkbox Columns.

Any assistance in combining these formulas would be greatly appreciated. Thank you.

Best Answer

  • Josh Martin
    Josh Martin ✭✭
    Answer ✓
    Options

    Problem solved. I've also incorporated automation to:

    • Automate Health aggregation one level higher (i.e., Project Name).
    • Automate Health for Milestones without nested Tasks to act as if they were Child rows.
    • Automate Health for Tasks with Sub-Tasks to act as if they were Parent rows.

    Completed Formula:

    =IF(AND(OR(Project@row = 1, Component@row = 1, Milestone@row = 1), COUNT(CHILDREN()) > 0), (IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))), (IF(COUNT(CHILDREN()) > 0, (IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))), (IF(OR(Status@row = "Complete", Status@row = "Descoped"), "Blue", IF(Status@row = "At Risk", "Red", IF(AND(Status@row = "", TODAY() < [End Date]@row), "Yellow", IF(AND(Status@row = "", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Requires Review", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() < [End Date]@row), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row <= 10), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row > 10), "Yellow", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Red", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Yellow"))))))))))))))))))))

    Columns Required:

    • Column Name = Project // Column Type = Checkbox
    • Column Name = Component // Column Type = Checkbox
    • Column Name = Milestone // Column Type = Checkbox
    • Column Name = Health // Column Type = Symbols (4 Colors for Red, Green, Yellow, & Blue)
    • Column Name = Status // Column Type = Dropdown List (6 Statuses for "Blank," "Not Started," "In Progress," "Requires Review," "At Risk," "Completed," & "Descoped")
    • Column Name = Start Date // Column Type = Date/Time
    • Column Name = End Date // Column Type = Date/Time
    • Column Name = Duration // Column Type = Duration

Answers

  • Josh Martin
    Options

    Text for the Formulas included here to assist with modifications:

    Formula 1:

    =IF(OR(Status@row = "Complete", Status@row = "Descoped"), "Blue", IF(Status@row = "At Risk", "Red", IF(AND(Status@row = "", TODAY() < [End Date]@row), "Yellow", IF(AND(Status@row = "", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Requires Review", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() < [End Date]@row), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row <= 10), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row > 10), "Yellow", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Red", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Yellow"))))))))))))))))

    Formula 2:

    =IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))

  • Josh Martin
    Josh Martin ✭✭
    Answer ✓
    Options

    Problem solved. I've also incorporated automation to:

    • Automate Health aggregation one level higher (i.e., Project Name).
    • Automate Health for Milestones without nested Tasks to act as if they were Child rows.
    • Automate Health for Tasks with Sub-Tasks to act as if they were Parent rows.

    Completed Formula:

    =IF(AND(OR(Project@row = 1, Component@row = 1, Milestone@row = 1), COUNT(CHILDREN()) > 0), (IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))), (IF(COUNT(CHILDREN()) > 0, (IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") > 0, COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", "Green")))), (IF(OR(Status@row = "Complete", Status@row = "Descoped"), "Blue", IF(Status@row = "At Risk", "Red", IF(AND(Status@row = "", TODAY() < [End Date]@row), "Yellow", IF(AND(Status@row = "", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Not Started", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "Requires Review", TODAY() >= [End Date]@row), "Red", IF(AND(Status@row = "In Progress", TODAY() < [End Date]@row), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row <= 10), "Green", IF(AND(Status@row = "Not Started", TODAY() < [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Not Started", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Green", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row <= 10), "Yellow", IF(AND(Status@row = "Requires Review", TODAY() < [Start Date]@row, Duration@row > 10), "Yellow", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row <= 10), "Red", IF(AND(Status@row = "Requires Review", [End Date]@row > TODAY(), TODAY() >= [Start Date]@row, Duration@row > 10), "Yellow"))))))))))))))))))))

    Columns Required:

    • Column Name = Project // Column Type = Checkbox
    • Column Name = Component // Column Type = Checkbox
    • Column Name = Milestone // Column Type = Checkbox
    • Column Name = Health // Column Type = Symbols (4 Colors for Red, Green, Yellow, & Blue)
    • Column Name = Status // Column Type = Dropdown List (6 Statuses for "Blank," "Not Started," "In Progress," "Requires Review," "At Risk," "Completed," & "Descoped")
    • Column Name = Start Date // Column Type = Date/Time
    • Column Name = End Date // Column Type = Date/Time
    • Column Name = Duration // Column Type = Duration
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Wow, thank you for sharing your solution!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!