IF(CONTAINS(OR( Help

Hi, I am trying to get the following to work:

I have a Sales Order Column where I have 3 possible entries: A date (Ex: 31Aug2019), a 2DP Order (Ex: 11111-1), and a QC Order (Ex: 11111-1 QC). I have two hidden checkboxes that will give a check if an order is QC or 2DP.

For QC, I just have =IF(CONTAINS("QC", [Sales Order]@row), 1, 0)

For 2DP, if I do =IF(CONTAINS("QC", [Sales Order]@row), 0, 1) I inevitably get the columns with Dates checked as well, which I do not want.

I'm trying to do a formula like this to solve it, but its not working. Anyhelp? Thanks!

=IF(CONTAINS(OR("QC", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")), [Sales Order]@row), 0, 1)

Find more posts tagged with

Comments

Paul Newcome

Here is what I would recommend...

=IF(AND(CONTAINS("-", [Sales Order]@row), FIND("QC", [Sales Order]@row) = 0), 1)

This will check the box if a dash is found but "QC" is not. Putting this in your 2DP checkbox column should do the trick.

ScionoftheNight

Ohhh I see, very clever solution. So it capitalizes on the fact that every single Sales Order has a "-" in it while a date never will, and if its not QC, then it has to be 2DP. Very nice, thank you, I haven't used the FIND function at all, seems very useful.

Paul Newcome

Ohhh I see, very clever solution.

Thank you!

So it capitalizes on the fact that every single Sales Order has a "-" in it while a date never will

Absolutely correct.

, and if its not QC, then it has to be 2DP.

Also correct.

Very nice, thank you,

Happy to help!

I haven't used the FIND function at all, seems very useful.

It is extremely useful. A lot of my own uses have been replaced with the CONTAINS function, but there are times where the FIND is just a little more efficient. The way it works is it produces a numerical value based on the position of the specified text within the text string. If more than one letter or character is specified to be searched for, it will return the position number of the first character where the string is found. It is also one of the few case sensitive functions.

For example...

[Target Cell]@row = "ABCDEFGabcdefg"

=FIND("A", [Target Cell]@row) will produce a 1.

=FIND("a", [Target cell]@row) will produce a 8.

=FIND("Z", [Target Cell]@row) will produce a 0 (zero) because it was not found.

=FIND("Gab", [Target Cell]@row) produces a 7 because that is where the first character of the specified string is found.

Using those examples above, we can say that if the FIND function when used to look for QC returns a 0 (zero) which means that QC was not found in the target cell, then by default it must be 2DP because the target cell also contains a dash (which is where the AND function comes in).

Another way of writing

FIND("QC", [Target Cell]@row) = 0

would be

CONTAINS("QC", [Target Cell]@row) = false

but that takes a few more key strokes, and honestly I am still learning to trust using the CONTAINS function that way. It is VERY new compared to the FIND function, so it is taking some getting used to. Haha

Paul Newcome

You could have also used

AND(CONTAINS(................), CONTAINS("2DP", [Target Cell]@row))

AND(CONTAINS(................), FIND("2DP", [Target Cell]@row) > 0)

Sign in to join the conversation!

Quick Links

Trending discussions

SUmmary in Report Widget
How can i create a Report widget that only shows a status with "red status" from my main data source? Do i need to create a separate data source on just my "red status"
Folder Level Sharing
Circling back to this….. Another year goes by and still no folder level sharing. Smartsheet developers don't listen to their clients. I have seen hundreds or posts and requests for this over the past 7-8 years. And still NOTHING!! This has been a huge issue for us as a company. As you can tell this is a sore spot for us…
Restore multiple cell changes from Snapshot
Today a team member accidentally changed several rows by either deleting the data or the row itself. Do I download a snapshot of each one to restore? Some of them didn't seem to be too helpful. What is the protocol for restoring this information? I've only done this before with the help of a representative and it was…