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.

Automating RYG And CHILDREN Formula

Mark Baldwin
edited 12/09/19 in Archived 2016 Posts

Hi 

 

I am looking for a formula that will show in the parent an RYG colour based on the children.

 

If there are any Red in the children, I want to show Red in the parent,

If there are no Red but there are Yellow in the children, Show Yellow in the parent.

If there are no Red or Yellow but there is Green in the children, Show Green in the parent.

If there are no Red, Yellow or Green but there are Blue in the children, Show Blue in the parent.

 

I am completely stumped on what formula I need and if this is even possible. 

 

Thanks in advance

Mark

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Mark,

     

    Try this:

     

    =IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", IF(COUNTIF(CHILDREN(), "Yellow") > 0, "Yellow", IF(COUNTIF(CHILDREN(), "Green") > 0, "Green", IF(COUNTIF(CHILDREN(), "Blue") > 0, "Blue", "NONE"))))

     

    Hope this helps.

     

    Craig

  • Hi Craig

     

    That worked like a charm! You saved me a lot of headache. 

     

    Mark

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Glad to help Mark.

     

    Craig

  • Rod McCracken
    edited 06/07/16

    My children RYG is in a different column.  How would I vary the formula above to read the other column data?

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Columns are referenced like this:

     

    [Column Name]:[Column Name]

     

    If the column name has no spaces or special characters, Smartsheet will usually change it to this:

     

    ColumnName:ColumnName

     

    but I always type the [].

     

    If you want to reference a set of rows, then you'd add the row numbers

     

    [Column Name]23:[Column Name]32

     

    for rows 23 to 32 inclusive.

     

    There is also absolute referencing

    [Column Name]$23:[Column Name]$32

     

    Check out the help articles if that is needed.

     

    Hope this helps.

     

    Craig

  • Hello! 

     

    I am working on creating a variation of this formula, but can't get it to work. 

     

    If there are any Red OR Yellow in the children rows, I want the parent row to show Yellow. 

    If there are only greens (no other color), I want the parent row to show Green. 

     

    How do I do the "or" statement? Thanks for any help! 

     

    Lindsey 

  • Hi LindseyC

    In my formula below, using RYG formatting, if there are no children the cell stays blank, if there is a red or yellow child, show yellow, or if there is a green child show green. 

     

    =IF(COUNT(CHILDREN([Action Status]48)) = 0, "", IF(OR(COUNTIF(CHILDREN([Action Status]48), "Red") > 0, COUNTIF(CHILDREN([Action Status]48), "Yellow") > 0), "Yellow", IF(COUNTIF(CHILDREN([Action Status]48), "Green") > 0, "Green")))

     

    Hope this helps

    Rod

  • LindseyC
    LindseyC
    edited 06/23/16

    Hi Rod! Yes, this is very helpful! Thanks for showing me how to add the "or" statement. Makes it very easy. 

     

     

This discussion has been closed.