Check for text value in multi-value checkbox cell

Andrew1983

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?

Andrew1983

OF course there was a much easier formula to use:

CONTAINS

=IF(CONTAINS("05 Production Release", [Item Type]@row), "True", "NO WAY")

Paul Newcome

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.

Andrew1983

@Andrée Starå You look like the resident SmartSheet expert! Any ideas here?

Many thanks,

Andrew

Kelly Moore

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

Andrew1983

@KDM

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!

Andrew1983

OF course there was a much easier formula to use:

CONTAINS

=IF(CONTAINS("05 Production Release", [Item Type]@row), "True", "NO WAY")

Paul Newcome

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.