IF(CONTAINS(OR( Help

ScionoftheNight

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)

 

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))

or

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