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.

column colors change from two different column status

I have a colored ball column that states if something is overdue from the due date to make the ball red. 

 

What I want is the ball to turn back green if the status changes to Completed.  I tried this

 

=IF(ISBLANK(Due3), "Gray", Status3 = "Complete", "Green", (IF(Due3 < TODAY(), "Red", IF(Due3 = TODAY(), "Yellow", IF(Due3 > TODAY(), "Green")))))

 

what am I doing wrong?  Everything is fine when I just use the due column but I can't quite firgure out the Status Complete changing it to green

 

 

Comments

  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭

    Cecelia,

     

    I'm getting close too.

     

    I'm getting this work, =IF(Status9 = "Complete", "Green", IF(Due9 > TODAY(), "Green", IF(Due9 < TODAY(), "Red", IF(Due9 = TODAY(), "Yellow", "Gray"))))

     

    However, if Due is blank, it defaults to Red.

     

    I tried to Add (NOT(ISBLANK, but can't get it work....

     

    =IF(OR(Status10 = "Complete", "Green", IF(NOT(ISBLANK((Due10 > TODAY(), "Green", IF(Due10 < TODAY(), "Red", IF(Due10 = TODAY(), "Yellow", "Gray")))))))

     

    I think I'm close.... maybe someone else can take it a step further....

     

  • Mike Andreas
    Mike Andreas ✭✭✭
    edited 02/21/17

    I'm finding this issue is a rather common one.  I answered a question with an end goal very similar to this one on another thread.

     

    https://community.smartsheet.com/discussion/does-anyone-have-formula-syntax-control-ryg-and-gray-symbols-sheet

     

     You will end up with the completed ball being blue instead of green - hope that is ok.  In my mind, green is for when something is on track or not started, but of course I realize people have different definitions.

     

    Also, it compares "Actual Percent Complete" to "Planned Percent Complete per Timeline", as opposed to a cell showing "Complete", as your request is.  It might be more than what what you're looking for, but I think a look is worth your time.

     

     

This discussion has been closed.