Formula required to prevent user entering multiple answers

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3158260%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EFormula%20required%20to%20prevent%20user%20entering%20multiple%20answers%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3158260%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EWhat%20formula%20can%20I%20use%20in%20my%20spreadsheet%20to%20Prevent%20a%20user%20from%20selecting%20more%20than%20one%20cell%20in%20a%20row%3F%20I%20have%20questions%20where%20the%20user%20can%20%22Strongly%20Agree%22%2C%20%22Agree%22%2C%20etc.%20which%20then%20gives%20a%20score%2C%20I%20need%20to%20prevent%20a%20user%20from%20selecting%20multiple%20answers%20so%20it%20doesn't%20skew%20the%20score.%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3158260%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
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:

=COUNTA($B2:$F2)=1

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.