Automating RYG balls to change colors

NaNa
NaNa ✭
edited 12/09/19 in Formulas and Functions

So i have parent cells and dependencies. The dependencies have color balls (red, yellow, green, grey). Here is what I want:

If one of the dependencies has a red color ball, I want the parent ball color to be red.

If all dependencies have a green ball, I want the ball for the parent to be green

If all dependencies have a yellow ball, I want the ball for the parent to be yellow

If all dependencies have a grey ball, I want the ball for the parent to be grey

Red ball in the midst of other color balls take precedence over all the other colors and the parent shows up as red. Next is yellow, grey then green.

Can you help with that?

Tags:
· Share on FacebookShare on Twitter

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    =if(countif(D1:D6,"Red")>0,"Red",if(Count(D1:D6)=countif(D1:D6,"Yellow"),"Yellow",if(Count(D1:D6)=countif(D1:D6,"Grey"),"Grey",if(Count(D1:D6)=countif(D1:D6,"Green"),"Green"

    This is exactly as you phrased it above, but that said there are a lot of gaps in this formula. Examples of gaps would be if one is grey and the rest green, it will be blank. I believe you mean any instead of all, in which case the formula below is what you want.

    =if(countif(D1:D6,"Red")>0,"Red",if(countif(D1:D6,"Yellow")>0,"Yellow",if(countif(D1:D6,"Grey")>0,"Grey",if(countif(D1:D6,"Green")>0,"Green"

    · Share on FacebookShare on Twitter
  • Sarah Thompson
    Sarah Thompson ✭✭
    edited 11/20/19

    I would tweak this slightly to use the children feature instead of D1:D6 in the event that you frequently add child rows. Otherwise if you're risking not counting all children.

    so =IF(countif(children() "Red")>0, "Red", etc.

    · Share on FacebookShare on Twitter
  • NaNa
    NaNa ✭

    Thank you!!! The second formula worked! It was exactly what I needed!

     

    Original

    =if(countif(D1:D6,"Red")>0,"Red",if(countif(D1:D6,"Yellow")>0,"Yellow",if(countif(D1:D6,"Grey")>0,"Grey",if(countif(D1:D6,"Green")>0,"Green"

    Changed it to fit my cells

    =IF(COUNTIF([Task Status]9:[Task Status]18, "Red") > 0, "Red", IF(COUNTIF([Task Status]9:[Task Status]18, "Yellow") > 0, "Yellow", IF(COUNTIF([Task Status]9:[Task Status]18, "Grey") > 0, "Grey", IF(COUNTIF([Task Status]9:[Task Status]18, "Green") > 0, "Green"))))

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You can also shorten it and account for any parent row without having to update specific row numbers.

     

    =IF(CONTAINS("Red", CHILDREN()), "Red", IF(CONTAINS("Yellow", CHILDREN()), "Yellow", IF(CONTAINS("Grey", CHILDREN()), "Grey", "Green")))

    .

    This can be used in any parent row with any number of children without any modification at all.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!