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)
Comments
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives