How to have Parent field Status update according to multiple Children field Status?
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.
Answers
-
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"))))
-
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")))))
-
Thanks for sharing your solution! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!