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

  • Paul Newcome
    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)

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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)

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    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?

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • Paul Newcome
    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")

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!