Using several conditions

Roshni Verappan

I am trying to build several conditions into my Smartsheet, is there a limit as to how many we can have?

This is the conditions I need to have set:

GRAY - when renewal status column status are selected: Awaiting Information, Bound (Lead), Bound (Subscription), Broker Contacted, Cancelled, Extension, Lapsed, Lapsed - Final Notice, Lapsed - Pricing, Lapsed - Unknown, Lapsed - Broker Lost, Lapsed - LMA, Off Risk - Capacity, Off Risk - Claims, Off Risk - Declined Referral, Off Risk - Other (Remarks), Quoted, Retired

This is the formula I am trying to use. which also includes an expiry condition too.

=IF([Renewal Status]@row = "Awaiting Information", "Gray", IF(Expiry@row < TODAY(45), "Red", IF(Expiry@row < TODAY(50), "Yellow", "Green"))))

Is this doable or is there another method I should be using to get the results I am looking for? Any assistance is greatly appreciated.

Genevieve P.

Hi @roshni verappan

Yes this is doable. However based on the number of potential statuses, would it be easier to say "turn gray if it's not these other values" instead?

In any case, all you'd need to do is add in an OR function right after the IF and list out all the possible selections. Try this:

=IF(OR([Renewal Status]@row = "Awaiting Information", [Renewal Status]@row = "Bound (Lead)",[Renewal Status]@row = "Bound (Subscription)", [Renewal Status]@row = "Broker Contacted", [Renewal Status]@row = "Cancelled", [Renewal Status]@row = "Extension", [Renewal Status]@row = "Lapsed", [Renewal Status]@row = "Lapsed - Final Notice", [Renewal Status]@row = "Lapsed - Pricing", [Renewal Status]@row = "Lapsed - Unknown", [Renewal Status]@row = "Lapsed - Broker Lost", [Renewal Status]@row = "Lapsed - LMA", [Renewal Status]@row = "Off Risk - Capacity", [Renewal Status]@row = "Off Risk - Claims", [Renewal Status]@row = "Off Risk - Declined Referral", [Renewal Status]@row = "Off Risk - Other (Remarks)", [Renewal Status]@row = "Quoted", [Renewal Status]@row = "Retired"), "Gray", IF(Expiry@row < TODAY(45), "Red", IF(Expiry@row < TODAY(50), "Yellow", "Green"))))

Keep in mind that IF statements read left-to-right and stop as soon as a condition is met, so if any of those statuses are true, it won't read the Expiry column at all.

Again, there may be a way to make this a lot shorter, but it depends on all the other potential selections in this dropdown column. For example, if you know that any status that says "Lapsed" should be gray, we can use the CONTAINS function instead of writing out every single one that contains "Lapsed":

=IF(OR(CONTAINS("Lapsed", [Renewal Status]@row), [Renewal Status]@row = "Awaiting Information", [Renewal Status]@row = "Bound (Lead)",[Renewal Status]@row = "Bound (Subscription)", [Renewal Status]@row = "Broker Contacted", [Renewal Status]@row = "Cancelled", [Renewal Status]@row = "Extension", [Renewal Status]@row = "Off Risk - Capacity", [Renewal Status]@row = "Off Risk - Claims", [Renewal Status]@row = "Off Risk - Declined Referral", [Renewal Status]@row = "Off Risk - Other (Remarks)", [Renewal Status]@row = "Quoted", [Renewal Status]@row = "Retired"), "Gray", IF(Expiry@row < TODAY(45), "Red", IF(Expiry@row < TODAY(50), "Yellow", "Green"))))

And again, if this will be true whenever any of the "Off Risk" options are selected, we can use CONTAINS again:

=IF(OR(CONTAINS("Lapsed", [Renewal Status]@row), CONTAINS("Off Risk", [Renewal Status]@row), [Renewal Status]@row = "Awaiting Information", [Renewal Status]@row = "Bound (Lead)", [Renewal Status]@row = "Bound (Subscription)", [Renewal Status]@row = "Broker Contacted", [Renewal Status]@row = "Cancelled", [Renewal Status]@row = "Extension", [Renewal Status]@row = "Quoted", [Renewal Status]@row = "Retired"), "Gray", IF(Expiry@row < TODAY(45), "Red", IF(Expiry@row < TODAY(50), "Yellow", "Green"))))

Let me know if this makes sense and if it works for you!

Cheers,

Genevieve

Roshni Verappan

Hi Genevieve,

thanks for your help that is really great so I can start thinking about this more and see what work.

Great idea also to use NOT status as per below:

However based on the number of potential statuses, would it be easier to say "turn gray if it's not these other values" instead?

assuming using a formula like this? =IF(NOT([Renewal Status]@row = "Work in Progress", "Gray"))

Thank you again

Genevieve P.

Hi @roshni verappan

Yes, very similar to what you have above, although you'd need to close off ) the NOT function before telling it what to do ("Gray").

Personally, I prefer to use <> to indicate "not" instead. You'd place it where you have the = sign. Like this:

IF([Renewal Status]@row <> "Work in Progress", "Gray"

Then if you have multiple to exclude, use the AND function instead of OR:

=IF(AND([Renewal Status]@row <> "Work in Progress", [Renewal Status]@row <> "Other Status", [Renewal Status]@row = "Another Status"), "Gray", IF(Expiry@row < TODAY(45), "Red", IF(Expiry@row < TODAY(50), "Yellow", "Green"))))

Cheers,

Genevieve