How can I have parent field Status change depending on multiple children field Status?

Daniel Thacker

I'm relatively new to SmartSheet and Excel as a whole and am trying to figure out how to automate some of the process. I have put together a formula to do part of what I want it to do, but I have a bad habit of going about things in the most complicated way possible and also don't know how to get the functionality for the final result I am looking for.

Columns are Task, Target Date, Date Completed, Done (checkbox), Status (TBD, Complete, On Schedule, Past Due). I am currently using this formula:

=IF([Target Date]@row = "TBD", "TBD", IF([Date Completed]@row <> "", "Complete", IF(ISTEXT([Target Date]@row), "", IF(ISBLANK([Target Date]@row), "", IF(MAX(0, TODAY() - [Target Date]@row) = 0, "On Schedule", "Past Due")))))

to have the Status show 'TBD' if the Target Date is "TBD"; 'Complete' if the Date Completed is not empty; 'On Schedule' if the Target Date is not empty and today's date is not past the Target Date; 'Past Due' if the Target Date is not empty and today IS past the Target Date.

I have the subtasks listed under a parent task. I would like to automate it so that when all of the sub tasks under a parent task are marked as complete, the parent task will reflect complete. If ANY of the sub tasks are marked Past Due, the parent task is marked Past Due, and if neither of those are true, the parent task to be marked On Schedule.

I apologize if this is confusing, can supply any additional information needed, and greatly appreciate any and all help in advance.

Thank you.

Daniel Thacker

Thank you for the thought and response Paul! I accidentally duplicated this discussion (see the other one here: https://community.smartsheet.com/discussion/100686/how-to-have-parent-field-status-update-according-to-multiple-children-field-status#latest)

I ended up using this formula which seems to be working well:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Past Due") > 0, "Past Due", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "On Schedule")), IF([Target Date]@row = "TBD", "TBD", IF([Date Completed]@row <> "", "Complete", IF([Target Date]@row = "", "", IF(MAX(0, TODAY() - [Target Date]@row) = 0, "On Schedule", "Past Due")))))

Paul Newcome

Try this:

=IF(COUNT(CHILDREN()) = 0, original_formula, IF(COUNTIFS(CHILDREN(), @cell = "Past Due")> 0, "Past Due", IF(COUNTIFS(CHILDREN(), @cell = "Complete") = COUNT(CHILDREN()), "Complete", "On Schedule")))

Daniel Thacker

Thank you for the thought and response Paul! I accidentally duplicated this discussion (see the other one here: https://community.smartsheet.com/discussion/100686/how-to-have-parent-field-status-update-according-to-multiple-children-field-status#latest)

I ended up using this formula which seems to be working well:

=IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Past Due") > 0, "Past Due", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "On Schedule")), IF([Target Date]@row = "TBD", "TBD", IF([Date Completed]@row <> "", "Complete", IF([Target Date]@row = "", "", IF(MAX(0, TODAY() - [Target Date]@row) = 0, "On Schedule", "Past Due")))))

Paul Newcome

@Daniel Thacker That's pretty much the same idea. We just flipped which runs first. Haha

I used:

IF there are no children, run child row formula, run parent row formula

You used:

IF there are children, run parent row formula, run child row formula