Formula that changes status symbol based on check box checked/un-checked

Options

I'm trying to create a formula that changes my status column to the red or green symbols.

I looked at some examples on the site, but wasn't able to get it to work on my sheet. I was trying to set up a formula around the following parameters..

If check box is checked then green

If check box is un-checked then red

I have used =IF([Renewal Date (Enter next Renewal Date)]@row > TODAY(), "Green", IF([Renewal Date (Enter next Renewal Date)]@row < TODAY(), "Red", "Yellow")) based on date column.

Thank you in advance for your help

Tags:

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Anthony Nicholas

    Are you looking to have the status changed based solely on the checkboxes, or are you trying to add it to the formula with the dates?

    To use solely with a checkbox, place this formula in the status column:

    =IF([your checkbox column]@row=1, "Green", "Red")

    you will need to update the formula above with your column name

    Will this work for you? If you are trying to add this to the other date criteria, please let me know if the condition for the checkbox is an AND condition (both must be true) or an OR condition (either is true). I'd be happy to help.

    Kelly

  • Anthony Nicholas
    Options

    That worked great! Thank you

    However after more thought, if I wanted Yellow for column 1 if checked, Green for column 2 if checked and Red for column 2 if un-checked. Is that possible?


    Thank you again

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Anthony Nicholas

    Yes, it's doable. And I need some clarification please- your definition for Yellow of Col1 offers challenges. The examples below show the 4 different possible combinations of your two checkbox columns. Could you clarify what color you would expect each of these examples to show? For example, in #2, is it Yellow, or is it Red? And #4? That will inform me of what priority you place of each example, which is important when building an IF statement


  • Anthony Nicholas
    Options

    Hi @Kelly Moore

    #1 Red (for no check mark on col 1) Col 2 would not be checked if col 1 is not checked

    #2: Yellow (for only col 1 checked)

    #3: Col 1 would always be checked first before column 2 is checked

    #4: Green (for both checked)


    Col 1 would act as the Completed Task check mark

    Col 2 would act as the Reviewed check mark

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey

    Thanks, just what I needed

    =IF([Col 2]@row = 1, "Green", IF([Col 1]@row = 1, "Yellow", "Red"))

    Will that work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!