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.

ChaitanyaK

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

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!!