Parent Status based on Children Harvy ball status.
Dear team,
I am trying to build a status update tracker, for teams to update each milestone. They are expected to update each milestone using the harvey ball status (0% /25% /50 % /75% /100%). I am trying to automate the status of parent based on children's Harvey ball status as below with logic as " If the status of all children in 0% the parent status will return as " Not started". If all the children are 100% the parent will return as " Completed". If the children has any other status individually the parent will return as " In progress"
=IF(COUNTIF(CHILDREN(),<0.25)=COUNT(CHILDREN()),"Not Started",IF(COUNTIF(CHILDREN(),=1)=COUNT(CHILDREN()),"Completed","In Progress")))
The above formula is returning #UNPARSEABLE". Can you please help me what is the error i am making here?. I am a newbie on smartsheets. you help is highly appreciated.
Best Answer
-
Carson Penticuff ✭✭✭✭✭With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:
=IF(COUNTIF(CHILDREN([BallCell]@row), "Empty") = COUNT(CHILDREN([BallCell]@row)), "Not Started", IF(COUNTIF(CHILDREN([BallCell]@row), "Full") = COUNT(CHILDREN([BallCell]@row)), "Completed", "In Progress"))
If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:
=IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), "Not Started", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), "Completed", "In Progress"))
In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.
Answers
-
Carson Penticuff ✭✭✭✭✭
With the symbols you are using, the options would typically be represented by the text entries [Empty, Quarter, Half, Three Quarter, Full]. In this case, this should work:
=IF(COUNTIF(CHILDREN([BallCell]@row), "Empty") = COUNT(CHILDREN([BallCell]@row)), "Not Started", IF(COUNTIF(CHILDREN([BallCell]@row), "Full") = COUNT(CHILDREN([BallCell]@row)), "Completed", "In Progress"))
If you do have your sheet setup somehow so that the entries are respresented by [0, 0.25, 0.50, 0.75, 1], then this should work:
=IF(COUNTIF(CHILDREN([BallCell]@row), < 0.25) = COUNT(CHILDREN([BallCell]@row)), "Not Started", IF(COUNTIF(CHILDREN([BallCell]@row), 1) = COUNT(CHILDREN([BallCell]@row)), "Completed", "In Progress"))
In either case, you will need to substitute [BallCell] with the name of the column with your symbols. The column name is not visible in your screenshot.
-
Thanks a million Carson!. Formula works like charm!!
Help Article Resources
Categories
Here is a possible solution for your formula:<\/p>
=IF(NOT(ISBLANK([Recovery Collected Date]@row)), \"Complete\", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), \"Incomplete\", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), \"Pending\", \"//www.santa-greenland.com/community/discussion/comment/\")))<\/p>
Please confirm I have your column names correct and adjust as needed.<\/p>
I hope this helps, and have a great weekend.<\/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":108264,"type":"question","name":"ROUNDDOWN","excerpt":"Question. I have a column that is calculating time. So in that column I get total minutes comparing a few other columns. I then want the total minutes to convert to hours and minutes. I am using this formula: =ROUNDDOWN([Total Down in Minutes]@row \/ 60, 0) + \":\" + MOD([Total Down in Minutes]@row, 60) The value it is…","snippet":"Question. I have a column that is calculating time. So in that column I get total minutes comparing a few other columns. I then want the total minutes to convert to hours and…","categoryID":322,"dateInserted":"2023-07-28T17:16:37+00:00","dateUpdated":"2023-07-28T17:17:45+00:00","dateLastComment":"2023-07-28T18:06:57+00:00","insertUserID":132978,"insertUser":{"userID":132978,"name":"David Noël","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20No%C3%ABl","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ujDCdw6fmPQ!WwFCX7KSXvo!WrOxILxFQ6w","dateLastActive":"2023-07-28T18:17:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":132978,"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-07-28T18:17:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":24,"score":null,"hot":3381134014,"url":"https:\/\/community.smartsheet.com\/discussion\/108264\/rounddown","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108264\/rounddown","format":"Rich","lastPost":{"discussionID":108264,"commentID":387877,"name":"Re: ROUNDDOWN","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387877#Comment_387877","dateInserted":"2023-07-28T18:06:57+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-07-28T18:17:54+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-07-28T18:18:15+00:00","dateAnswered":"2023-07-28T18:02:47+00:00","acceptedAnswers":[{"commentID":387875,"body":"
See if this works for you:<\/p>
=ROUNDDOWN([Total Down in Minutes]@row \/ 60, 0) + \":\" + IF(MOD([Total Down in Minutes]@row, 60) < 10, \"0\" + MOD([Total Down in Minutes]@row, 60), MOD([Total Down in Minutes]@row, 60))<\/p>"},{"commentID":387876,"body":"
Carson, thank you. This makes complete sense and hate I didn't think of the last part of that formula. Thank you again!<\/p>"},{"commentID":387877,"body":"
Happy to help!<\/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":108250,"type":"question","name":"formula help - using two cells","excerpt":"looking to fill in answer to the question - if \"Cell\" is less than one then put 1 in formula cell. in our case - we fill in the acreage of the lot. if this is less than 1 acre we need to put a \"1\" in the upgrade septic line Still learning formulas and this one is too advance for me!","snippet":"looking to fill in answer to the question - if \"Cell\" is less than one then put 1 in formula cell. in our case - we fill in the acreage of the lot. if this is less than 1 acre we…","categoryID":322,"dateInserted":"2023-07-28T14:49:49+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T17:55:24+00:00","insertUserID":164264,"insertUser":{"userID":164264,"name":"cris3","title":"Office Admin","url":"https:\/\/community.smartsheet.com\/profile\/cris3","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T17:55:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-07-28T17:54:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":18,"score":null,"hot":3381125113,"url":"https:\/\/community.smartsheet.com\/discussion\/108250\/formula-help-using-two-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108250\/formula-help-using-two-cells","format":"Rich","lastPost":{"discussionID":108250,"commentID":387874,"name":"Re: formula help - using two cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387874#Comment_387874","dateInserted":"2023-07-28T17:55:24+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-07-28T17:54:59+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-07-28T17:48:00+00:00","dateAnswered":"2023-07-28T17:21:03+00:00","acceptedAnswers":[{"commentID":387863,"body":"
Hi @cris3<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =IF(Acreage@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF(Acreage@row < 1, 1))<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic weekend!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
