#### 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(OR Formula Help

Options
✭✭✭✭✭✭
edited 12/09/19

What am I doing wrong?

If Required Column, Rows 23-34 are "Yes" OR "No" THEN show "Yes" symbol, if NOT show "On Hold"

This is the formula I'm using:

=IF(OR(Required23 = "Yes", Required23 = “No", Required24 = "Yes", Required24 = "No", Required25 = "Yes", Required25 = "No", Required26 = "Yes", Required26 = "No", Required27 = "Yes", Required27 = "No", Required28 = "Yes", Required28 = "No", Required29 = "Yes", Required29 = "No", Required30 = "Yes", Required30 = "No", Required31 = "Yes", Required31 = "No", Required32 = "Yes", Required32 = "No", Required33 = "Yes", Required33 = "No", Required34 = "Yes", Required34 = "No"), "Yes", "Hold")

But I'm getting the "unparseable" error

[See ScreenShot]

• If Rows 23-34 "Required" (Symbol Column) are "Yes" or "No", I want Row 22, "Required" column to show "Yes"
• If Rows 23-34 "Required" (Symbol Column) are "On Hold" or Blank, I want Row 22, "Required" column to show "On Hold"

Thanks in advance! Sorry for flooding the community with formula questions lately. I'm learning, and you all give the best help!

Tags:

• ✭✭✭✭✭✭
Options

=IF(OR(Required23 = "No", Required24 = "No", Required25 = "No", Required26 = "No", Required27 = "No", Required28 = "No", Required29 = "No", Required30 = "No", Required31 = "No", Required32 = "No", Required33 = "No", Required34 = "No"), "No", IF(AND(Required23 = "Yes", Required24 = "Yes", Required25 = "Yes", Required26 = "Yes", Required27 = "Yes", Required28 = "Yes", Required29 = "Yes", Required30 = "Yes", Required31 = "Yes", Required32 = "Yes", Required33 = "Yes", Required34 = "Yes"), "Yes", "Hold"))

Also tried this formula per Smartsheet Support. But it isn't working either. I don't receive an error but it puts "no" in row 22 if 23-34 have a no, It puts "on hold" if 23-34 have an "on hold" and it puts "yes" if 23-34 are all yes. This is not what i'm looking for

• ✭✭✭✭✭✭
Options

Laura,

Try this:

=IF(OR(COUNTIF(Required23:Required34, "") > 0, COUNTIF(Required23:Required34, "Hold") > 0), "Hold", "Yes")

Craig

• Employee
Options

Craig -

I like your suggestion.  But what if someone enters something like "tbd" in the Required column?  Based on your direction, I came up with the following which would also handle that case.

=IF(COUNT([Order Entry Notes]23:[Order Entry Notes]33) - COUNTIF(Required23:Required33, OR(@cell = "Yes", @cell = "No")) = 0, "Yes", "Hold")

John

• ✭✭✭✭✭✭
Options

John,

Thank you.

"tbd" was not part of the requirements.

Since the column is a symbol column (similar to a drop down), the blank check should cover it. You solution may be inaccurate when the [Order Entry Notes] is blank. When we have covered all of the likely user errors, we must let the users find the others.

Craig

• ✭✭✭✭✭✭
Options

Thanks Craig! That worked. I've drove myself crazy trying to create a messy formula today!

• ✭✭✭✭✭✭
Options

I don't recommend driving oneself crazy. Been there. Done that. Didn't like it.

You are welcome.

Craig

• ✭✭✭✭✭✭
edited 04/19/17
Options

Haha! Thanks Craig!

This discussion has been closed.