How to pull and display the value of a cell from its children using string types
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?
Best Answer
-
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
Answers
-
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
-
worked! Thank you!
