Status update from children rows
Hi Smartsheets Team,
I have a column "Task" that has parent and children rows.
I am wanting the parent row "Status" to update when the children row "Status" changes.
For example, Parent row (Real Estate) would be "Not Started" until one of the Children Rows (Pro Forma, LOI, PSA, etc) are marked as "Processing", "Pending", "Completed", "N/A" or "Canceled" then Parent row (Real Estate) would become "Processing". When all children are marked "Completed" or "N/A" or "Canceled" the Parent row is changed to "Completed".
I have this thus far, but can't figure out when all children are either Complete &/or N/A &/or Canceled but one is Not Started? I'd like for Parent status to show as Pending, but Parent shows Not Started.
=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started"))))
If I use the following formula, even when all are Complete, N/A or Canceled, Parent still says Processing:
=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started"))))))
I appreciate your time.
Answers
-
You would continue with the nested IF statement adding more IFs as needed until all are accounted for.
-
I can't figure out how to get the Parent to Completed if all children are Completed &/or N/A &/or Canceled & if they are not all Completed &/or N/A &/or Canceled, the parent would be either processing or not started
=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN(Status@row)), "Completed", "Not Started", IF(COUNTIF(CHILDREN(), "N/A") = COUNT(CHILDREN(Status@row)), "Completed", "Not Started", IF(COUNTIF(CHILDREN(), "Canceled") = COUNT(CHILDREN(Status@row)), "Completed", "Not Started")))))
-
Instead of
COUNTIF(CHILDREN(), "Completed")
you would use
COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled"))
-
Many thanks @Paul Newcome
-
Happy to help. 👍️
-
How about if all are complete &/or N/A &/or Canceled but one is Not Started? The parent shows Not Started.
-
You would use the IF/CONTAINS combo similar to your "Processing" piece.
-
This is the formula I'm using:
=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started"))))
When I add the following, the parent never changes to "Completed"
IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing"
-
It depends on where exactly you are adding it, but I venture to say you are adding it before the "Completed" COUNTIF. Nested IFs work from left to right and stop on the first true value. So if even one child cell contains "Completed", it is going to flag as true and stop evaluating there. You most likely need to rearrange the order of your IF statements.
-
I hate to keep asking but, I can't get this formula to work
=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing"))))))
-
Try moving the "Not Started" piece to the end of the formula.
-
If I use the following formula, even when all are Complete, N/A or Canceled, Parent still says Processing:
=IF(CONTAINS("Processing", CHILDREN()), "Processing", IF(CONTAINS("Pending", CHILDREN()), "Processing", IF(CONTAINS("Completed", CHILDREN()), "Processing", IF(CONTAINS("N/A", CHILDREN()), "Processing", IF(CONTAINS("Canceled", CHILDREN()), "Processing", IF(COUNTIF(CHILDREN(), OR(@cell = "Completed", @cell = "N/A", @cell = "Canceled")) = COUNT(CHILDREN(Status@row)), "Completed", "Not Started"))))))
-
Right. That goes back to my previous comment:
"Nested IFs work from left to right and stop on the first true value. So if even one child cell contains "Completed", it is going to flag as true and stop evaluating there. You most likely need to rearrange the order of your IF statements."
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!