Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

RYG Parent/Child Row Status Correlation

Options
amclaren
amclaren ✭✭
edited 12/09/19 in Archived 2017 Posts

Hello,

I'm trying to have a formula in my parent row status (red, yellow, green) where it will answer with a "summary" of all the children status.

For example, if all 5 children rows were red then the parent row status would turn red.

If 3 children rows were yellow and 2 children rows were red then the parent row status would turn yellow.

But, I'm trying to get the formula to also take into account blanks. Right now blanks are counted as reds, but I would like them to be counted as blanks.

So, if 5 children rows were red and 7 children rows were blank then the parent row status would turn blank.

Is this possible???? 

THANK YOU!!!

Tags:

Comments

  • Adam Overton
    Options

    It is. You can get the numbers by using COUNTIF(CHILDREN(), "Red") and "Green" and "Yellow". With those counts you can determine how you want to format them.

    Sometimes it's easier to build some extra columns to get those summary separately, and then pull the summary together in the actual parent column. Once you get it working, you can decide if you want to combine into one giant formula or just hide the other columns.

     

  • Kara Lumley
    Options

    This might help you get started - if most of the children are red, make the parent red, if most of the children are green, make the parent green, otherwise, make the parent yellow.

    =IF(AND(COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") > COUNTIF(CHILDREN(), "Red")), "Green", IF(AND(COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Red") > COUNTIF(CHILDREN(), "Yellow")), "Red", "Yellow"))

    You could add in a condition for " " (indicating blank) or replace yellow with " " .

    Best,

    Kara

     

     

  • ruizdeaz36321
    Options

    Hi Kara, I need my parent row to show a blue ball when the children are blank, red for everything else. I'm probably slow now because I have tried so many options and built so many formulas today. 

    This is the last I tried but I can't see what I did wrong: 

    IF(ISBLANK(COUNTIF(CHILDREN(), " ")), "Blue", "Red")

    Many thanks in advance! 

  • Kara Lumley
    Options

    Hello - give this a try:

    =IF(COUNTIF(CHILDREN(), ISBLANK(@cell)) > 0, "Blue", "Red")

This discussion has been closed.