Feb 12 2022 12:46 PM
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.
Feb 12 2022 09:23 PM
@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:
=COUNTA($B2:$F2)=1
Copied it across and down.
Feb 13 2022 09:58 AM
@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?
Feb 13 2022 10:28 AM
@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.
Feb 13 2022 12:38 PM
Feb 13 2022 01:05 PM
@John_A1 That I got, but I just don't understand why. Perhaps you can show how your schedule looks like.