Child Parent code for schedule not working

I have reviewed the formula helper and used their code and change it up a bit but not working as it should, when I change to blocked or on Hold the Parent is not changing, here is the code

=IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Blocked") > 0, "Blocked", IF(COUNTIF(CHILDREN(), "On Hold") > 0, "On Hold", "Not Started"))))

I need it to do the following

If all children are complete than it should be complete

if any of the children are In progress it should be in progress

if any of the children are on hold it should be on hold

if any of the children are blocked it should be blocked.

If all of the children are not started it should be not started

Caroline

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Nested IF formulas read from left to right and stop on the first true value. So if you have In Progress in one child row and On Hold in another child row, it will register a true value with the In Progress argument, output that, then stop evaluating. If you want On Hold to "override" In Progress, you would need to adjust the order in which your IFs are nested.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!