Adding another parameter to IF(OR) with multiple conditions

Paul Bilbro

I have a formula working exactly how I want it to, unless I add one more parameter (checking a checkbox), in which case I don't even know where to begin. Would that be an IF(AND(OR))? If so, can someone help me with placement?

The current formula which pulls from [Docs Check Date Helper], [Cargo Ready Helper], [Latest ETA Helper] columns and based on those values will display a Red, Yellow, or no Harvey Ball in the [Action Needed] column. I want it to display as it currently is, unless you check the [Docs Check Date Done] = "Checked" AND [Docs Check Date] = "Red" or "Yellow" AND [Cargo Ready Helper] = "Green" AND [Latest ETA Helper] = "Green"

If [Cargo Ready Helper] = "Red" or "Yellow" or [Latest ETA Helper] = "Red" or "Yellow" continue to display the appropriate Harvey Ball until they change to "Green"

Here is the current formula:

=IF(OR([Docs Check Date Helper]@row = "Red", [Cargo Ready Helper]@row = "Red", [Latest ETA Helper]@row = "Red"), "Red", IF(OR([Docs Check Date Helper]@row = "Yellow", [Cargo Ready Helper]@row = "Yellow", [Latest ETA Helper]@row = "Yellow"), "Yellow", IF(OR([Docs Check Date Helper]@row = "Green", [Cargo Ready Helper]@row = "Green", [Latest ETA Helper]@row = "Green"), "")))

IF Docs Check Date Helper @ Row = "Red", Display "Red"

IF Cargo Ready Helper @ Row = "Red", Display "Red"

IF Latest ETA Helper @ Row = "Red", Display "Red"

IF Docs Check Date Helper @ Row = "Yellow", Display "Yellow"

IF Cargo Ready Helper @ Row = "Yellow", Display "Yellow"

IF Latest ETA Helper @ Row = "Yellow", Display "Yellow"

IF Docs Check Date Helper @ Row = "Green", Display ""

IF Cargo Ready Helper @ Row = "Green", Display ""

IF Latest ETA Helper @ Row = "Green", Display ""

UNLESS

If Docs Check Date Done @ Row is "Checked"

AND

IF Docs Check Date Helper @ Row = "Red" OR IF Docs Check Date Helper @ Row = "Yellow"

AND

IF Cargo Ready Helper @ Row = "Green"

IF Latest ETA Helper @ Row = "Green"

DISPLAY

"" (nothing)

Here is current layout/status

Thanks for any help!!

Danielle Arteaga

I have never wanted a formula to work more than I want this one to work for you. I actually saved the worksheet I used to test this monster, just in case I ever need it. 😀

I'm sure you already know this, but may be worth mentioning for future travelers that the trick here was just figuring out the order of conditions by determining which ones would definitely "close the loop" when a condition was met.

Oh - and magnifying the screen to find any missing parentheses, braces and commas.

=IF(AND([Cargo Ready Helper]@row = "Green", [Latest ETA Helper]@row = "Green", OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Docs Check Done]@row = 1), "", IF(OR([Docs Check Date Helper]@row = "Red", [Cargo Ready Helper]@row = "Red", [Latest ETA Helper]@row = "Red"), "Red", IF(OR([Docs Check Date Helper]@row = "Yellow", [Cargo Ready Helper]@row = "Yellow", [Latest ETA Helper]@row = "Yellow"), "Yellow", "")))

Danielle Arteaga

I have never wanted a formula to work more than I want this one to work for you. I actually saved the worksheet I used to test this monster, just in case I ever need it. 😀

I'm sure you already know this, but may be worth mentioning for future travelers that the trick here was just figuring out the order of conditions by determining which ones would definitely "close the loop" when a condition was met.

Oh - and magnifying the screen to find any missing parentheses, braces and commas.

=IF(AND([Cargo Ready Helper]@row = "Green", [Latest ETA Helper]@row = "Green", OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Docs Check Done]@row = 1), "", IF(OR([Docs Check Date Helper]@row = "Red", [Cargo Ready Helper]@row = "Red", [Latest ETA Helper]@row = "Red"), "Red", IF(OR([Docs Check Date Helper]@row = "Yellow", [Cargo Ready Helper]@row = "Yellow", [Latest ETA Helper]@row = "Yellow"), "Yellow", "")))

kirstie858

FYI - a checkbox evaluates to true or false. Also, I assume [Docs Check Date] was supposed to be [Docs Check Date Helper] in paragraph 2.

I like to break down complicated formulas into parts. I take the "unless...." bit to mean that overrides the first formula, which I'll refer to as Action Needed v1.

IF(new criteria = true, then "", else Action Needed v1)

So the new bit it this:

=IF(AND([Docs Check Done]@row = true, OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Cargo Ready Helper]@row = "Green", [Latest ETA Helper]@row = "Green"), "", [Action Needed v1]@row)

Action Needed v2: From here, you can sub in the old formula:

=IF(AND([Docs Check Done]@row = true, OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Cargo Ready Helper]@row = "Green", [Latest ETA Helper]@row = "Green"), "", IF(OR([Docs Check Date Helper]@row = "Red", [Cargo Ready Helper]@row = "Red", [Latest ETA Helper]@row = "Red"), "Red", IF(OR([Docs Check Date Helper]@row = "Yellow", [Cargo Ready Helper]@row = "Yellow", [Latest ETA Helper]@row = "Yellow"), "Yellow", IF(OR([Docs Check Date Helper]@row = "Green", [Cargo Ready Helper]@row = "Green", [Latest ETA Helper]@row = "Green"), ""))))

Test it out, see if that evaluates to your desired response.

(PS I see that Danielle answered while I was still working on the logic! Looks like she re-evaluated the whole statement, and wrote you a better one. :-))

Paul Bilbro

@Danielle Arteaga @kirstie858

@kirstie858 you are correct about paragraph two - should have been [Docs Check Date Helper] - good eye!

YOU LADIES ROCK! They both work which I love. Always more than one way to look at something (perspective).

It is true, I needed to understand what was going to be the final parameter to "close the loop." IF (checked) then everything else. I appreciate both of your insight by explaining the thought process so I can look at it that way in the future. By the time I had this beast (agree to keep this one in the back pocket @Danielle Arteaga) written out, my brain had tapped out for the day lol.

Great to get some fresh, intelligent eyes on the subject. First post on this sub and WOW so exciting to have this as a resource.

THANK YOU THANK YOU. Love the support!

Paul Bilbro

@Danielle Arteaga @kirstie858

Ok, there is one little bug (maybe I didn't outline in my original post). When [Docs Check Date Helper] = "Red" AND [Docs Check Done] = 1 AND [Cargo Ready Helper] = "Yellow" OR [Latest ETA Helper] = "Yellow", [Action Needed] still displays "Red". It should disregard the "Red" in [Docs Check Date Helper] and look to the "Yellow" in [Cargo Ready Helper] or [Latest ETA Helper].

Please see image attached.

SmartSheet Docs Check Done Question 2.png

Thanks!

Danielle Arteaga

@Paul Bilbro - Let me make sure I understand what you wrote above, because I'm confused by your last sentence.

Are you saying that IF EITHER Cargo Ready Helper is Yellow or Latest ETA Helper is Yellow, then Action Needed should be Red no matter what the status of Docs Check Date Helper is? What do you mean by "disregard the Red in [Docs Check Date Helper] and look to the Yellow in [Cargo Ready Helper] or [Latest ETA]? Because if you want to "disregard" the Red in Docs Check Date Helper, I'm not sure why you'd include it in the logic.

Paul Bilbro

@Danielle Arteaga - Sorry for the confusion.

Disregard was probably the wrong term. The logic you wrote displays everything correctly, besides when [Docs Check Date Helper] = "Red" AND [Docs Check Done] = 1 AND [Cargo Ready Helper] = "Yellow" OR [Latest ETA Helper] = "Yellow"

Based on the above statement, [Action Needed] should display "Yellow" but it is currently displaying "Red"

Paul Bilbro

@Danielle Arteaga I solved it! Sometimes it helps to think out loud! Please see final formula below:

=IF(AND([Cargo Ready Helper]@row = "Green", [Latest ETA Helper]@row = "Green", OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Docs Check Done]@row = 1), "", IF(AND([Cargo Ready Helper]@row = "Yellow", OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Docs Check Done]@row = 1), "Yellow", IF(AND([Latest ETA Helper]@row = "Yellow", OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Docs Check Done]@row = 1), "Yellow", IF(OR([Docs Check Date Helper]@row = "Red", [Cargo Ready Helper]@row = "Red", [Latest ETA Helper]@row = "Red"), "Red", IF(OR([Docs Check Date Helper]@row = "Yellow", [Cargo Ready Helper]@row = "Yellow", [Latest ETA Helper]@row = "Yellow"), "Yellow", "")))))

Two new pieces of logic I added:

IF(AND([Cargo Ready Helper]@row = "Yellow", OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Docs Check Done]@row = 1), "Yellow",

IF(AND([Latest ETA Helper]@row = "Yellow", OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Docs Check Done]@row = 1), "Yellow",

Thanks for all the help, it was fun to have a breakthrough moment when everything worked properly!

Danielle Arteaga

=IF(AND([Cargo Ready Helper]@row = "Green", [Latest ETA Helper]@row = "Green", OR([Docs Check Date Helper]@row = "Red", [Docs Check Date Helper]@row = "Yellow"), [Docs Check Done]@row = 1), "", IF(ORAND([Docs Check Date Helper]@row = "Red", [Cargo Ready Helper]@row = "Red", [Latest ETA Helper]@row = "Red"), "Red", IF(OR([Docs Check Date Helper]@row = "Yellow", [Cargo Ready Helper]@row = "Yellow", [Latest ETA Helper]@row = "Yellow"), "Yellow", "")))

Try the above modification. Now, Docs Check Date Helper does not influence the Action Needed column alone. It acts only in combination with Reds in Cargo Ready Helper or Latest ETA Helper. I think this does what you want, but I will admit I'm not feeling super confident I follow all the logic.

Danielle Arteaga

Our posts crossed digital paths. :-) Really happy you figured it out. This was fun! These are like puzzles - feels good to solve them.

Paul Bilbro

@Danielle Arteaga puzzles indeed! Fun when you solve, (can be) frustrating when you are attempting! Wouldn't have gotten here without you and @kirstie858.

Thanks again, 1st Smartsheet community post... A+++++++++