If/Or Statement

Hello all,

I am relatively new to Smartsheet and have hit a wall in trying to automate a RYG. I am looking to change the color by listing multiple possibilities from a range of cells. My idea was to create a separate column for the formula, hoping to return a "1" or a "0". Then I could add a simple formula in the RYG column to change accordingly based on that "0" or "1".

The below formula returns the dreaded #INVALID OPERATION entry. Can anybody see where I am going wrong? In the below picture, each column has it's own drop down list to populate the cells. I chose the values from each column in the below formula.

Thank you for any and all help with this.


=IF(OR([Security Agreement]57:[Freight Forwarder]57 = "Pending", [Security Agreement]57:[Freight Forwarder]57 = "Re Quote 30 Days Prior", [Security Agreement]57:[Freight Forwarder]57 = "MCC Pending", [Security Agreement]57:[Freight Forwarder]57 = "3rd Party Pending"), "0", "1")

Formula Error.JPG


  • Jim Hook
    Jim Hook ✭✭✭✭✭✭

    I think the problem is that the arguments for an OR function cannot be an array. In other words, each of your arguments includes an array of cells from [Security Agreement]57 to [Freight Forwarder]57, or something similar but at least two cells, and the argument can only have one cell to compare against at a time. You need to do a separate part of the OR function for each cell you're looking for "Pending" in.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!