Skip to content
  • Try for free

    • Archived 2017 Posts

    Archived 2017 Posts

    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.

    Select RYG color based on other cells

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

    I have a sheet with checkboxes in 3 columns, and I'd like to have an RYG symbol in a separate cell be red if 1 box is checked, yellow if 2 and green if 3.  If no boxes are checked I'd like to have no RYG symbol visible.  I am assuming this needs to be some combination of IF and COUNTIF, but I have tried different combinations and appealed to Smartsheet itself and just can't figure this out.  Do you guys have any suggestions?  Thanks!

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Comments

    • ✭✭✭✭
      edited 03/29/17

      Holly,

       

      I recommend having a look at this help article which outlines how to write formulas to interact with the different column types: https://help.smartsheet.com/articles/2476091-frequently-asked-questions-about-using-formulas

       

       

      For example:

      =IF(Value1 > 7, "Red", IF(Value1 > 3, "Yellow", "Green"))

      · Share on FacebookShare on Twitter
    • Community Champion

      Assuming your checkbox columns are called checkboxcolumn1, checkboxcolumn2, and checkboxcolumn3, then you could paste the following formula into your RGB symbol formatted column in row 1 and it should do the trick. 

       

      =IF(countifs([checkboxcolumn1]1,1,[checkboxcolumn2]1,1,[checkboxcolumn3]1,1) = 1, "Red", IF(countifs([checkboxcolumn1]1,1,[checkboxcolumn2]1,1,[checkboxcolumn3]1,1) = 2, "Yellow", IF(countifs([checkboxcolumn1]1,1,[checkboxcolumn2]1,1,[checkboxcolumn3]1,1) = 3, "Green"))) 

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Andrew (and Mike)

       

      Mike's solution does not work.

      All of the COUNTIFS in his formula will return 0 or 1. COUNTIFS is not counting each of the check boxes, it is counting how many of a range is matching all of the criteria.

      Because the range is only one cell, the return will be either 0 or 1.

       

      So, let's first change the solution if the columns are consequetive:

       

      =IF(COUNTIFS([checkboxcolumn1]1:[checkboxcolumn3]1, 1) = 1, "Red", IF(COUNTIFS([checkboxcolumn1]1:[checkboxcolumn3]1, 1) = 2, "Yellow", IF(COUNTIFS([checkboxcolumn1]1:[checkboxcolumn3]1, 1) = 3, "Green")))

       

      This works because now the range is 3 cells.

       

      The non-consequetive solution is ugly as each COUNTIFS in Mike's formula needs to be replaced by 3 COUNTIF's

       

      IF((COUNTIF(cell1, 1) + COUNTIF(cell2,1) + COUNTIF(cell3,1))=1,"Red", ...and so on.

       

      Craig

       

       

      · Share on FacebookShare on Twitter
    • Community Champion

      Good Catch Craig! I should test my theories before I type them! Sealed

       

      · Share on FacebookShare on Twitter
    • ✭✭

      Thank you so much!  I think I went through all these steps at some point, got as far as the 'consecutive' solution and stalled!  The checkboxes are in non-consecutive columns, so Craig's second solution is working.  Smile  Thank you all again!

      · Share on FacebookShare on Twitter
    This discussion has been closed.

    Quick Links

    Categories

    Trending Posts