Data validation for multiselect dropdown lists?

Karoly Ban Matei

I am embedding a Smartsheet form in an online training module, to collect and validate the answers in the absence of an LMS. For a single dropdown, I found it easy - I create a question column, in which I collect the answer, and an answer column, in which I validate the answer. To validate the answer I use a simple IF formula, such as:

=IF([field]="correct answer", 1,0). This returns a value of 1 if the answer is correct and 0 if it is incorrect, and the values go in a formula that calculates the percentage of correct answers.

But I had some questions that were multi-select dropdowns. If two answers are correct, what is the correct syntax for an IF/AND to return a value of 1 if and only if both correct answers have been selected?

(I found a workaround, but is not elegant. I revert to a single select and label the questions with A, B, C, etc, and then create a D answer that says "Both A and C are correct". Selecting D returns the value 1, so I can work with this, but I want an elegant solution that works for a multi-select dropdown.

Did anybody found a solution to this issue?

Heather Duff

@Karoly Ban Matei

Glad it worked! It took me a hot minute to get it figured out just right. 😊 Have a great week.

Bassam Khalil

Hi @Karoly Ban Matei

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Heather Duff

Hi @Karoly Ban Matei ,

Using AND(CONTAINS( may be your best bet. Try something like this:

=IF(AND(CONTAINS("CorrectAnswer1", Field@row), CONTAINS("CorrectAnswer2", Field@row)), 1, 0)

Hope this helps!

Best,

Heather

Karoly Ban Matei

Thanks, @Heather D , it works!

It does have a small drawback - as long as the two correct answers are selected, it always returns a correct value, even if a third option is selected.

Heather Duff

@Karoly Ban Matei

How about this, assuming there are only 4 options in the question:

=IF(OR(CONTAINS("WrongAnswer1", Field@row), CONTAINS("WrongAnswer2", Field@row)), 0, IF(AND(CONTAINS("CorrectAnswer1", Field@row), CONTAINS("CorrectAnswer2", Field@row)), 1, 0))

This basically says they will get 0 points if either of the wrong answers is selected; otherwise, they will get 1 point if both correct answers are selected; otherwise, they will get 0 points.

Karoly Ban Matei

Thanks, @Heather D, that works! I get the logic behind it, but being 20 years removed from any programming the syntax eluded me.

Thanks again,

Karoly

Heather Duff

@Karoly Ban Matei

Glad it worked! It took me a hot minute to get it figured out just right. 😊 Have a great week.