Formula required to prevent user entering multiple answers

New Contributor

What formula can I use in my spreadsheet to Prevent a user from selecting more than one cell in a row? I have questions where the user can "Strongly Agree", "Agree", etc. which then gives a score, I need to prevent a user from selecting multiple answers so it doesn't skew the score.

5 Replies

@John_A1 You could do that with data validation, where each cell in the relevant range on a row checks if there already are answers on that row. In the attached example, I used this custom validation rule in B2:


Copied it across and down.

Screenshot taken on a Mac, though similar for the PC version.Screenshot taken on a Mac, though similar for the PC version.


@Riny_van_Eekelenthank you the formula works, the only problem I have is that I was using the data validation to create a list for a dropdown of 1 to 5, and now this doesn't work, is there a way of combining custom and list?

@John_A1 Not sure I follow. Why would you have dropdowns allowing 1 to 5 when you have 5 columns of which you only allow one answer? But perhaps I misunderstand.

I also used the validation data to create a list that had the numbers 1 to 5 to use as a dropdown menu for each cell as this is then used to analyse answers to the questions

@John_A1 That I got, but I just don't understand why. Perhaps you can show how your schedule looks like.