How to pull and display the value of a cell from its children using string types

Cy Sack

Hi,

I'm using a Smartsheet to organize OKRs. I'd like a parent goal to display the worst status of all the projects underneath it (children rows). Meaning if a OKR has 5 projects under it, 3 of which are On Track, 1 At Risk, and 1 Off Track, the OKR should display the Off Track value from the 1 project, since that's worst of the projects' statuses. What's the right type of formula for this, since the values of the projects' status is a string, not a number?

https://app.smartsheet.com/b/publish?EQBCT=4f7b9f686e444ba188e4f3f13545b9aa

Alejandra

Hi @Cy Sack,

Try this formula:

=IF(COUNT(CHILDREN(OKR@row)) > 0, IF(COUNTIF(CHILDREN(), "Off Track") > 0, "Off Track", IF(COUNTIF(CHILDREN(), "At Risk") > 0, "At Risk", IF(COUNTIF(CHILDREN(), "On Track") > 0, "On Track", " "))))

This formula looks to see if any of the children rows contain a status of "Off Track", if so, it will return "Off Track". If not, it will then look for a status of "At Risk", and it will return "At Risk" in the parent row if there is at least one child row that is at risk and so on.

If needed, more information on these functions can be found here: https://help.smartsheet.com/functions

Alejandra

Hi @Cy Sack,

Try this formula:

=IF(COUNT(CHILDREN(OKR@row)) > 0, IF(COUNTIF(CHILDREN(), "Off Track") > 0, "Off Track", IF(COUNTIF(CHILDREN(), "At Risk") > 0, "At Risk", IF(COUNTIF(CHILDREN(), "On Track") > 0, "On Track", " "))))

This formula looks to see if any of the children rows contain a status of "Off Track", if so, it will return "Off Track". If not, it will then look for a status of "At Risk", and it will return "At Risk" in the parent row if there is at least one child row that is at risk and so on.

If needed, more information on these functions can be found here: https://help.smartsheet.com/functions

Cy Sack

worked! Thank you!