I have created a questionnaire that has 3 columns where an answer can be provided (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. Any ideas?
Last edited by 2571; 11-23-2012 at 10:09 AM.
This is definitely not possible using formulas.
You would have to use VBA to get that to happen.
Alternatively, you could have other ways for users to mark their options. An in-cell dropdown, or option buttons will do the trick without having to resort to VBA.
Don't forget to click the little star to the left of this post if you feel I helped!
Taming the Excel dragon... www.TheExcelphile.com
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:
Please Login or Register to view this content.
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
2571,
You can make a custom rule within Data Validation which could prevent entry in 2 cells if a 3rd cell is not blank. If eg you're dealing with cells A1, B1 & C1, the following:
as a custom DV rule for A1 will prevent any entry in A1 unless both B1 & C1 are empty. You can change the cell references as necessary to prevent entry in cells B1, then C1, unless both other cells are empty.
Please Login or Register to view this content.
Hope this helps.
EDIT: sorry SixthSense, didn't see your (undoubtedly cleaner!) response while I was typing.
Brendan.
__________________________________________________________________________________________________
Things to consider:
1) You can thank any poster by clicking the * at the left of a helpful post.
2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
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
maybe
Formula:Please Login or Register to view this content.
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Hi vlady
Now I am slightly confused as it will not let me insert a 1 into any column??
Regards
Stacey
works for me..
select the columns to be validated first... then go to data->validation enter the syntax above with the range of the selected columns=AND(COUNTBLANK(D1:H1)=4,COUNTIF(D1:H1,1)=1)
if there still error
try this one
=IF(OR(COUNTIF($A$1:$E$1,1)<>1,COUNTBLANK($A$1:$E$1)<4),FALSE,TRUE)
Last edited by vlady; 11-22-2012 at 08:51 PM.
This is what I have entered and it still won't let me put anything in the cell, clearly I am doing something wrong, I just don't know what
AND(Countblank(D11:F11)=4,CountIF(D11:F11,1)=1)
sorry forgot to include info about $ sign
$D$11:$F$11
No still not working get the same error and will not let me add a 1 into any column. I am off to bed now but I will have a look again in the morning as I may be doing something completely daft and just too tired to see it.
Thank you for all of your help
@ 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:Please Login or Register to view this content.
If the input data is anything then
Formula:Please Login or Register to view this content.
Lovely sixthsense that seems to have worked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks