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 Answers
-
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!!
Answers
-
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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!