Parent Status based on Children Harvy ball status.

ChaitanyaK

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.

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.

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.

ChaitanyaK

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