Try Validation.
Select the range that needs to be protected to enter only one "1" in the range.
select the range and press Alt+D+L and Settings>>Allow>>Custom>>Formula
Formula:
=COUNTIF($A2:$E2,1)=1
Give ok
Now the range A2 to E2 allow the user to input only one 1 in the range.
Change the A2:E2 to your range.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Brilliant thank you Sixthsense that works perfectly
Hi Sixthsense
I have just realised that using the formula that you provided doesn't stop me adding any other number in to the 2nd and 3rd columns when I have already added the 1 in the first.
when I add a 1 into the first column and I go to add another 1 in the second column it will not allow me to do it and throws up an error message which is exactly what I want it to do however if I add a 1 to the first column then add a 3 to the second column it happily allows me to do this without throwing up any errors is there anyway I can stop this from doing that??
Regards
Stacey
@ 2571, I have suggested the formula based on your below lines
I thought that the cell values will be marked with 1 only instead of any other characters.(an answer is provided by entering a 1 in the appropriate column) however what I am trying to work out is whether I can apply a formula to the cells to stop the user from entering another 1 in another column causing duplicate answers per question
If you are sure that the input values are only Numbers then use the below one
Formula:=COUNT($A2:$E2)=1
If the input data is anything then
Formula:=COUNTA($A2:$E2)=1
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks