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.

IF statement formatting based on data in multiple columns

Lindsay Morton
Lindsay Morton ✭
edited 12/09/19 in Archived 2015 Posts

Hi,

 

I have a sheet that includes Start Date, Finish Date and Status Columns. I'd like to create an IF formula that considers data in all three columns in order to create color coded balls as shown here (https://www.smartsheet.com/blog/support-tip-automate-RYG-balls).

 

Specifically, I'd like the logic to be:

 

If Start is after TODAY, "Blue"

If Start is before TODAY and status is "Not Started", "Yellow"

If Finish is after TODAY and status is "In Progress" or "On Track", "Green"

If Finish is before today and status is not "Complete", "Red"

 

I think its possible to apply this kind of nested logic in Excel using AND and OR functions, but I can't make it work the same way in SmartSheet. 

 

Thanks!

· Share on FacebookShare on Twitter

Comments

  • Travis
    Travis Employee
    edited 12/04/15

    Hi Lindsay, here's the formula which will adher the the logic you wanted:

     

    =IF([Start Date]1 > TODAY(), "Blue", IF(AND([Start Date]1 < TODAY(), Status1 = "Not Started"), "Yellow", IF(AND([End Date]1 > TODAY(), OR(Status1 = "In Progress", Status1 = "On Track")), "Green", IF(AND([End Date]1 < TODAY(), NOT(Status1 = "Complete")), "Red"))))

     

    I noticed there is not a RYGB ball if the task is marked as complete. If you want to add this, add another IF statement to the beginning of the formula, like this:

     

    =IF(Status1 = "Complete", "Green", IF([Start Date]1 > TODAY(), "Blue", IF(AND([Start Date]1 < TODAY(), Status1 = "Not Started"), "Yellow", IF(AND([End Date]1 > TODAY(), OR(Status1 = "In Progress", Status1 = "On Track")), "Green", IF(AND([End Date]1 < TODAY(), NOT(Status1 = "Complete")), "Red")))))

    · Share on FacebookShare on Twitter
  • John Peterson
    John Peterson ✭✭

    I used the IF(AND(),Y,N) recently and found it worked as well as the excel equivalent

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

Categories