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

Daniel Thacker
edited 02/06/23 in Smartsheet Basics

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.

Best Answer