Task Status - Roll up to Parent

Options
CJU
CJU ✭✭✭✭✭

If a task has a number of sun-tasks, and their status is updated (Not started, In Progress, Complete), how can the overall task (parent) status be automatically updated.

Craig

Answers

  • Michael Pappas
    Michael Pappas ✭✭✭✭
    Options

    A Nested IF formula should work.

    =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started"))

    This says:

    IF the number of child rows = the number of child rows marked as Complete -> Complete

    IF not -> IF either any child row contains In Progress OR at least 1 child is Complete AND at least 1 child is Not Started -> In Progress

    IF not -> Not Started

  • CJU
    CJU ✭✭✭✭✭
    Options

    Hi Michael

    If I have a status column that has manual drop down, with Not Started, In Progress, Complete - would this formula sit in the parent task cell, but in a separate column?

  • Michael Pappas
    Michael Pappas ✭✭✭✭
    Options

    The formula as I wrote it would sit in the parent task cell in the same column. You can put formulas in columns that are drop downs. However, if anyone used the dropdown to change the value in the parent column, it would delete the formula.

  • CJU
    CJU ✭✭✭✭✭
    Options

    Thanks Michael

    That works.

    Is there anyway to lock the parent cell so that changes cannot be made?

    When counting the total number of tasks, does Smartsheet ignore the parent, i.e. only counting the children?