Checking box if form submitter has selected certain options from a multi-select drop down

Justin Guest

Hi,

I am new to Smartsheet and have a form where there are about one hundred options in a multi-select drop down, they are subjects of expertise the person could speak on.

15 of the options are in the technology category of expertise. I want a checkbox to be automatically checked if the person selects one or more of the 15 topics in this category.

What formula would I use to do this?

I have tried the below function with no success, it puts a 1 instead of a checkmark and I cannot figure out how to have the formula reflect multiple topics instead of one specific one.

=IF(HAS("Technology Topic of Expertise", [Topics Aligned]71), "1", "0")

Thank you!!

Paul Newcome

You would need to use an OR function to combine the different options into a single statement.

The reason it is displaying a 1 instead of checking the box is because of the quotes. Quotes around the numbers converts it to text, but the checkbox is run off of numerical values of 1 and 0.

=IF(HAS("Technology Topic of Expertise", [Topics Aligned]71), 1, 0)

=IF(OR(HAS("Technology Topic of Expertise", [Topics Aligned]71), HAS("Text 2", [Topics Aligned]71), HAS("Text 3", [Topics Aligned]71), HAS("Text 4", [Topics Aligned]71)), 1, 0)

Justin Guest

Hi Paul,

Thanks for the reply!

I wanted to clarify - it seems as if this formula only works if the person chose one of the 15 topics I specified. Is there a way to have the box be automatically checked if the person chooses one or more of these 15 topics?

Paul Newcome

Switch the HAS out for a CONTAINS.

=IF(OR(CONTAINS("Technology Topic of Expertise", [Topics Aligned]71), CONTAINS("Text 2", [Topics Aligned]71), CONTAINS("Text 3", [Topics Aligned]71), CONTAINS("Text 4", [Topics Aligned]71)), 1, 0)

Justin Guest

Hi Paul,

Really appreciate the help here, it all worked out.

Is there a formula I could insert that would use a similar Contains formula you showed above AND also list the topics the formula found?

Thanks so much.

Paul Newcome

What do you mean? Could you provide a sample of the desired output?

Justin Guest

Sure!

Example: A person filling out the form selects to be aligned to: Topic A, Topic B, Topic C, and Topic D

I want a formula that can search for Topic A and Topic B. If these topics are present, I want Topic A and Topic B to be listed in the column next to it and not include Topic C and Topic D.

Does that make sense?

Thanks!

Paul Newcome

I think so, but would you be able to provide a "mocked up" example with the data manually entered so that I can see exactly what you are trying to accomplish?

Justin Guest

Here is the output I would like to make it more clear. Thanks!

Paul Newcome

You could use something like this...

=IF(CONTAINS("Topic A", [Topics Aligned]71), "Topic A") + CHAR(10) + IF(CONTAINS("Topic B", [Topics Aligned]71), "Topic B")