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.

Formula with multiple conditions and logic

Brian Carcich
edited 12/09/19 in Archived 2016 Posts

Help! I'm trying to create a formula that automates RYG balls based on many different factors across multiple cells. I'm having trouble because the "IF" formula that I created is stopping at the first point of validation that it finds to be true and instead, I need it to keep moving on the validate all points. 

 

Here's what I'm trying to make happen in one cell:

If (Begin) has a checkbox, yellow, if (Begin) doesn't have a checkbox, blank. AND THEN if (Due) is blank, gray. AND THEN if (Actual) contains date and (Due) is blank, green. AND THEN if (Actual) is less than or equal to (Due), green. AND THEN if (Actual) is greater than (Due), red. 

Tags:

Comments

  • Here's an example of one of mine that might help. 

     

    =IF(ISDATE([Date Sent to Customer]5), "Blue", IF(ISDATE([Date Released to SAM]5), "Green", IF(NETWORKDAYS(TODAY(), [Date Needed]5) <= 2, "Red", IF(NETWORKDAYS(TODAY(), [Date Needed]5) <= 4, "Yellow"))))

  • This will need to be a nested IF statement with AND conditions. 

     

    This help center article will help you build it: http://help.smartsheet.com/customer/en/portal/articles/775363-using-formulas

     

    If you are having trouble, show us the formula you created and we can troubleshoot with you!

     

    I would attempt to build it for you, but I dont have the time at the moment Innocent

  • Brad Jones
    Brad Jones ✭✭✭✭✭✭
    edited 10/02/16

    @Ian

     

    Thanks so much for posting your formula.  I had a formula I thought would work with just two conditions, but in order for it to work right I needed four conditions defined.  Your example saved my day.  With mine, i wanted to track safety bypasses, so I needed the status to stay red unless the bypass was removed, and ONLY if the bypass was removed on a day >= to the day it was installed.

     

    =IF(

    AND(ISBLANK(Description15), ISBLANK(Signal15), ISBLANK(Location15), ISBLANK(Reason15), ISBLANK([Installed Date]15))

    , "", 

    IF(ISBLANK([Removed Date]15)

    , "Red",

    IF(([Installed Date]15 <= [Removed Date]15)

    , "Green",

    IF(([Installed Date]15 > [Removed Date]15)

    , "Red"

    ))))

     

This discussion has been closed.