Task Status - Roll up to Parent

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

Michael Pappas

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

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

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

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?