Parent Status based on Children Harvy ball status.

ChaitanyaK
ChaitanyaK ✭
inFormulas and Functions

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.

Picture1.png


· Share on Facebook Share on Twitter

Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    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.

    · Share on Facebook Share on Twitter

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭
    Answer ✓

    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.

    · Share on Facebook Share on Twitter
  • ChaitanyaK
    ChaitanyaK ✭

    Thanks a million Carson!. Formula works like charm!!

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Greetings @brownrobe<\/a>,<\/p>

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":[]}">

Trending in Formulas and Functions

  • Combining IF Formula for Blank/ Not Blank Cells

    I want to create a formula that provides the below statuses: -Complete: Based on "Collected Date" not null -Incomplete: Based on "Collected Date" null and "Antcipated Collected Date" null -Pending: Based on "Anticipated Collcted Date" not null and "Collected Date" null Below is what I have, but it's unparseable:…

  • ROUNDDOWN

    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…

  • formula help - using two cells

    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!