Check for text value in multi-value checkbox cell
Hello all,
I have a smartsheet where one cell (Item type) is a multiple check-box type, with the following values:
00 Project, 01 Submittal Task, 02 Vendor Drawing, 03 Product Data, 04 Samples, 05 Production release.
The smartsheet is a tracker for our submittals for our company. The engineer will enter a line item (i.e. Cabinets on job XYZ, and then check off the relevant submittals/actions that line item might need).
I have a date field i am trying to have ONLY show when "05 Production release" is ONE OF the checked boxes in the "Item type" field.
I am getting an #INVALID VALUE error from my formula below (using just TRUE/FALSE for now to test it):
=IF(FIND("05 Production release", [Item Type]@row, 0), "TRUE", "FALSE")
Is there a way to search for that text string correctly in an IF statement?
Best Answers
-
OF course there was a much easier formula to use:
CONTAINS
=IF(CONTAINS("05 Production Release", [Item Type]@row), "True", "NO WAY")
-
I was going to suggest CONTAINS.
The problem with the FIND function is that if the total number of characters in the cell being evaluated is less than the number of characters in the string you are searching for then you will get the error.
=FIND("jiafbgiuagaihgpaiuhgpauihg", [Column Name]@row)
will throw an error if the text in [Column Name]@row is "abc".
If you were to have adjusted your FIND to only search for the beginning two digits "05", it should have worked for you.
Answers
-
-
Hey @Andrew Hobby
The FIND function "Returns the starting position (the number of characters in) of a string within text. Any number returned means the Find function found something.
Try this
=IF(FIND("05 Production release", [Item Type]@row, 0)>0, "TRUE", "FALSE")
cheers
-
Hmm... yes, when I re-read the definition you pasted I realized that FIND doesn't return a true/false as I had presumed. I tried your formula but was still getting the #INVALID ERROR.
However, when I modified it to not have the string start of 0, I now get a "FALSE" readout.. now I'm just confused. I thought your formula would work!
-
OF course there was a much easier formula to use:
CONTAINS
=IF(CONTAINS("05 Production Release", [Item Type]@row), "True", "NO WAY")
-
I was going to suggest CONTAINS.
The problem with the FIND function is that if the total number of characters in the cell being evaluated is less than the number of characters in the string you are searching for then you will get the error.
=FIND("jiafbgiuagaihgpaiuhgpauihg", [Column Name]@row)
will throw an error if the text in [Column Name]@row is "abc".
If you were to have adjusted your FIND to only search for the beginning two digits "05", it should have worked for you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!