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

Holly D
Holly D
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!

Comments

  • Andrew DeCounter
    Andrew DeCounter ✭✭✭✭
    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"))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    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"))) 

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

    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

     

     

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

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

     

  • 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!

This discussion has been closed.