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




  • Tim Meeks
    Tim Meeks ✭✭✭✭✭✭



    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.




     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.