Checking box if form submitter has selected certain options from a multi-select drop down
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!!
Answers
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
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)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
What do you mean? Could you provide a sample of the desired output?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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!
-
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?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Here is the output I would like to make it more clear. Thanks!
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!