How to have Parent field Status update according to multiple Children field Status?

Daniel Thacker

Hello all,

I'm relatively new to smartsheet and excel and was hoping to get some help automating part of my sheet. I also have a bad habit of going about things the most complicated way possible, so there may be a much simpler way to accomplish this.

The columns involved are: Status(TBD, Complete, On Schedule, Past Due), Done (Checkbox), Tasks, Target Date, Date Completed. I am 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 auto populate Status depending on: If 'Target Date' = "TBD" then 'Status' = "TBD"; if 'Date Completed' is not empty then 'Status' = "Complete"; if 'Target Date' is not empty and is later than today then 'Status' = "On Schedule"; if 'Target Date' is not empty and is before today then 'Status' = "Past Due"

I have an automation setup so that when the 'done' checkbox is completed, the 'Date Completed' is populated with today's date. You can also enter 'Date Completed' manually. This makes it so that neither me nor my Team have to update Status manually.

Currently, I am having to manually update the 'Date Completed' to update the parent status for a group of subtasks. What I would like to happen is that if ALL subtasks = "Complete" then parent = "Complete"; if ANY subtask = "Past Due" then parent = "Past Due"; otherwise parent = "On Schedule"

I apologize if I made this confusing and am happy to provide any additional details. Thank you kindly for any and all help in advance.

Daniel Thacker

Well, I've made it this far and should be able to make this work. I wonder if there is a cleaner way to perform my original formula though and also if there is a way to achieve this result using a column formula instead of having to apply to specific rows individually?

=IF(COUNTIF(CHILDREN(test152), "Past Due") > 0, "Past Due", IF(COUNTIF(CHILDREN(test152), "Complete") = COUNT(CHILDREN(test152)), "Complete", "On Schedule"))

UPDATE:

I also cleaned up the original formula a bit:

=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"))))

Daniel Thacker

UPDATE:

I believe I have it figured out:

=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")))))

Genevieve P.

Hi @Daniel Thacker

Thanks for sharing your solution! 🙂