Parent Row Formula based on Child Row Status
Hello,
I am trying to write a formula where my child row status roll up to the parent row for an auto update. My row values are listed below and how I want the logic to roll up:
Not Started - all children are “Not Started”
In Progress - all children are "In Progress", Ahead of Schedule", "Behind Schedule"
Complete - all children are “Complete” or “ Canceled”
Ahead of Schedule
Behind Schedule
Canceled
Based on responses I found in the Community, I can up with the below formula but it is not working.
=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN("Not Started")), IF(COUNTIFS(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", “Canceled”), IF(COUNTIFS(CHILDREN(), "In Progress") = COUNT(CHILDREN()), “In Progress”. “ Ahead of Schedule”, “Behind Schedule”))
Any help would be appreciated! Thank you in advance!!!!
Dan
Answers
-
The fastest way would be to make a helper column. You can't make a dropdown column a column formula and keep the dropdown which means a fair amount of copy/paste on down the road. This gives you a column that does a thing (my preference).
I'm sure someone can simplify this.
helper column =IF(COUNT(CHILDREN()) > 0, IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Ahead of Schedule") = COUNT(CHILDREN()), "Ahead of Schedule", IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress", IF(COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Complete", "n/a")))))), IF(Status@row = "Not started", "Not Started", IF(Status@row = "In Progress", "In Progress", IF(Status@row = "Ahead of Schedule", "In Progress", IF(Status@row = "Behind Schedule", "In Progress", IF(Status@row = "Complete", "Complete", IF(Status@row = "Cancelled", "Complete", "n/a")))))))
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!