Formula to update status on a parent row based on child row with OR statement
Hi - I need a formula to update the status on the parent row based on status values in the child rows below. I am wresting with this formula and hoping someone on here can point me in the right direction. I found this post which is exactly what I'm looking for but there was never an answer to her last comment about needing to add the logic if "all children are any of those 3 status's then Completed." which is the part I'm struggling with.
https://community.smartsheet.com/discussion/formula-update-status-parent-row-based-any-child-row
The four statuses are:
- Not Started
- In Progress
- Completed
- Cancelled
So here's the logic for populating the parent's status (I think I got it all, let me know if I missed anything!):
- If all children are Not Started, the parent is Not Started
- If all children are In Progress, the parent is In Progress
- If all children are Completed, the parent is Completed
- If all children are Cancelled, the parent is Cancelled
- If all children are Completed OR Cancelled, the parent is Completed
- If one child is In Progress, the parent is In Progress
Many thanks!
Comments
-
Frank Falco ✭✭✭✭✭✭=IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started",
IF(COUNTIF(CHILDREN(), "In Progress") = COUNT(CHILDREN()), "In Progress",
IF(COUNTIF(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed",
IF(COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled",
IF(COUNTIF(CHILDREN(), "Completed")+COUNTIF(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Complete",
IF(COUNTIF(CHILDREN(), "In Progress")>0,"In Progress","---")))))The "---" is for when none of the conditions are true, such as one is Not Started and others are Completed. You could add another condition for that case or others.
✅Did my post help answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
-
Paul Newcome ✭✭✭✭✭✭You can actually remove the second set of if all of the children are "In Progress" because regardless of whether it is all or just one, you have the "At least on is in progress" criteria established further down your list. You can also do the same with "All children are completed". Since you have that number factored into the completed/cancelled set, it is redundant. You can also cut out one of the other sets by accounting for the rest of your criteria and making the last possibility the "else" value for if none of the others are true.
One scenario I don't see factored in though is if you have some completed/cancelled and some not started. Your formula would return a blank for this because there are no tasks that are "In Progress" even though the parent task is.
Here is another option you could try if you like.
What it says:
1. If all children are "Not Started" then "Not Started".
2. IF all children are "Cancelled" then "Cancelled".
3. If all children are either "Cancelled" or "Completed" then "Completed". (This will not trigger if all are only "Cancelled" because we specified that scenario before this one. The IF statements will stop running at the first true value).
4. If there is any other combination not already specified then "In Progress" to include but not limited to
4a. At least one "In Progress"
4b. Combination of "Not Started" and "Completed" and/or "Cancelled"
.
=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell= "Completed",@cell= "Cancelled")) = COUNT(CHILDREN()), "Completed", "In Progress")))
-
Amazing - thank you so much! I never would have come up with that. And thank you for thinking of that extra scenario of if the children are a combination of Not Started, Completed or Cancelled. Appreciate the help!
-
Paul Newcome ✭✭✭✭✭✭
Happy to help!
Sometimes there are numerous solutions to the same issue. My personal approach is to make it as short as possible. Other people may find it easier to be as specific as possible. What matters is what works best for you.
-
stephanie.blake67241 ✭✭✭✭✭
Can you please help me make a tweak to this formula? If any of the children are "Canceled" then the parent should be "Canceled.
-
Paul Newcome ✭✭✭✭✭✭
Sure thing!
=IF(CONTAINS("Canceled", CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Completed") = COUNT(CHILDREN()), "Completed", "In Progress")))
-
stephanie.blake67241 ✭✭✭✭✭
Thanks but that didn't quite work. In the formula you provided, if all children are "Complete" the parent row shows "In Progress"...
-
Paul Newcome ✭✭✭✭✭✭
Can you provide a screenshot? That doesn't sound right. That particular portion first counts all of the children that are "Completed". Then it counts all of the children. If both of those numbers match, then it will populate "Completed". Do you have any blank rows in your Children?
Are you using Completed or Complete? The text must be an exact match for it to work. I noticed you used Complete in your comment but the formula uses Completed.
-
stephanie.blake67241 ✭✭✭✭✭
That works, thanks!
-
Paul Newcome ✭✭✭✭✭✭
-
m_dorsey ✭
Would you be willing to help me tweak your original formula to also include a scenario of making the parent "On Deck" if there is a combination of only "Not Started" and "On Deck" children below?
I'm specifically referencing this formula:
=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), OR(@cell= "Completed",@cell= "Cancelled")) = COUNT(CHILDREN()), "Completed", "In Progress")))
-
Hi Everyone
I'm also having terrible problems trying to emulate this same formula, I have read, and re-read this thread and tried so many times but I keep getting #UNPARSEABLE error. I simply cannot fathom for the life of me what the issue is. I'm not the greatest at formulas at the best of times but the advice from@Frank Falcoseemed the easiest for me to copy, but still no success. Obviously I was replacing some of the status names in " " for my own ;) I didn't just copy and paste
I need to do pretty much the same thing as@christy.thompson106676.
I've spent a good 2 hours trying to fix this but cannot. I would be grateful if someone could please give me the EXACT formula (I don't understand @row for example), of what formula I need to put in my parent rows to return the status result. I've actually given up.
So now what I need help with please is the below please:
IfANY ONEof the children is 'In Progress', then the parent is In Progress
If In Progress does not appear inanyof the children rows, then just leave the parent status cell blank.
This will serve my purpose for now as I've spent far too long trying to fix this and I have to deliver a working dashboard by tomorrow morning which pulls in all my In Progress Parent rows from 9 different sheets.
Many thanks in advance!
-
Andrée Starå ✭✭✭✭✭✭Can you paste the formula you’re trying?
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or somescreenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too,[email protected])
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå
Workflow Consultant / CEO @WORK BOLD
✅了我的帖子(s)帮助回答你的问题或溶胶ve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå| Workflow Consultant / CEO @WORK BOLD
W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
-
Could you please help me@Frank Falco.
All balls will start off Red, and team will manually change the Children to Green balls once task is complete. The hopeful outcome once all Children balls are complete, the Parent ball will automatically change to Green. Is there a formula you can do for the balls?
-
JennV ✭@Paul NewcomeHi, Sorry to tag on to this discussion. After reading probably 50 of these posts, this is the formula that is the closest that works the best for me but I'm having 2 problems.
- When all children are "Completed" then "In Progress" - supposed to be "Completed"
- If all children are either "Cancelled" or "Completed" then "In Progress" - supposed to be "Completed."
I added a 4th Status "On Hold" and it's working correctly: all children "On Hold" then "On Hold" and any other combination the parent would be "In Progress." Here's the formula:
=IF(COUNTIFS(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIFS(CHILDREN(), "Cancelled") = COUNT(CHILDREN()), "Cancelled", IF(COUNTIFS(CHILDREN(), "On Hold") = COUNT(CHILDREN()), "On Hold", IF(COUNTIFS(CHILDREN(), OR(3 = "Complete", 3 = "Cancelled")) = COUNT(CHILDREN()), "Complete", "In Progress")))
Thanks for the help!
Jenn
Help Article Resources
Categories
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
