If Statement for Colored Balls

Options

Hello,


I am trying to create an IF statement that says IF there is one or more red ball in the range below then the green ball turns red OR If there are one or more yellow balls I would like the green ball to turn yellow. Otherwise, the ball stays Green. So far here is a simple attempt just trying with the first portion of the function, but I am unable to get this to work.

=IF(Health2:Health19 >=1 Red, Red)

Can somebody please help me?



Tags:

Answers

  • Etienne Mermillod
    Etienne Mermillod ✭✭✭✭✭
    Options

    The contains formula seems to work fine:

    if any value is red, the cell be be red

    else if any value is yellow the cell will be red

    else the cell will be green :


    =IF(CONTAINS("Red", Health:Health), "Red", IF(CONTAINS("Yellow", Health:Health), "Yellow", "Green"))

  • RingJake
    RingJake ✭✭✭✭✭
    Options

    Excellent @Etienne Mermillod Thank you!


    Now as a follow-up would it be possible to have instead of a range being:

    Health:Health or Health1:Health20

    It would be possible to always have this applied to the next 19 rows under the top level task? That way the function can stay together when it is sorted as well as easy to copy and paste for new groupings following the format in the screenshot.

  • Etienne Mermillod
    Etienne Mermillod ✭✭✭✭✭
    Options

    Yeah, specifying a limited range is also possible (just make sure then to not include the current row in the range, such as :

    =IF(CONTAINS("Red", Health2:Health20), "Red", IF(CONTAINS("Yellow", Health2:Health20), "Yellow", "Green"))

  • RingJake
    RingJake ✭✭✭✭✭
    Options

    Hey @Etienne Mermillod,


    Yes, but the range does not seem stay together when I sort. This is impacted other columns

    I have as well.



  • RingJake
    RingJake ✭✭✭✭✭
    Options

    If there was a way to group or lock certain rows that would ideally be the way to accomplish this?

  • Etienne Mermillod
    Etienne Mermillod ✭✭✭✭✭
    Options

    There isn't something you can do this kind of lock.

  • RingJake
    RingJake ✭✭✭✭✭
    Options

    Hi @Etienne Mermillod,


    What it looks like I really need is the ability to sort only by the Parent rows, while keeping the child rows in the same order. No ability to do this?

  • Etienne Mermillod
    Etienne Mermillod ✭✭✭✭✭
    Options

    Adding another row, that will be sued for sorting could be a solution, but I need to know more about the requirements.

  • RingJake
    RingJake ✭✭✭✭✭
    Options

    Hey @Etienne Mermillod,


    It seems if I drag the parent row to where it needs to be it will take the child cells with it in the desired order.


    But the automation of this would be to sort it. As you can see in the screenshot above I have many teams providing updates to a program, these updates make up a status and % complete. I would like to be able to sort only by the parent's status or % complete without changing the order of the children. Additionally, to have the Parents in alphabetical order without changing the children.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @RingJake

    I hope you're well and safe!

    To add to Etienne's excellent answer/advice.

    Have you explored using a report instead?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Indira Buike
    Options

    Hi! piggybacking on this question, I need it that if I have 2 red balls in the status column then the health column would be red, if I have 1 red ball then is yellow and if I have none red balls then is green. I'm stuck in the formula, pls help :(

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Indira Buike

    I've answered your other similar question.

    =IF(COUNTIF(Status3:Status6, "Green") = 4, "Green", IF(COUNTIF(Status3:Status6, "Green") = 3, "Yellow", IF(COUNTIF(Status3:Status6, "Green") = 2, "Red", "")))

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!