Hi all,
I would like to format the blue rectangle C2:C5 so that only one cell can accept "yes", and when it is inputted " yes" in one cell others cells to be disabled. I attach an example file.
Hi all,
I would like to format the blue rectangle C2:C5 so that only one cell can accept "yes", and when it is inputted " yes" in one cell others cells to be disabled. I attach an example file.
If it is possible without VBA :-)
If you put this into your data validation, you will see Yes if no other cells in column C already contain Yes.
=IF(COUNTIF(C:C,"Yes")=0,$G$2,INDIRECT("NoList"))
The second part just puts a "dummy" list in so that the drop down list appears as blank.
Excel is a constant learning process and it's great to help each other. If any of us have helped you today, a click on the "reputation" star on the left is appreciated.
It works but I would like the customer can change its choose because if he wrongly entered "yes" to "501-1000" to have the possibility to correct let say to "up to 500"
If I understand you correctly, you want the customer to be able to choose Yes in another cell, which would then blank out the cell in which they previously chose Yes?
I think this would only be possible with VBA as the validation is checking the active cell and making a decision on whether to allow an entry or not. Validation can't change the values in other cells in the range. Would you like me to create the VBA or will you leave it that the user will need to delete one Yes before they can enter another?
If you disable the cells with selection of "Yes", how do you enable them if a correction is required? and surely this defeats the objective of your first requirement ?
Yes, the objective is to make the same effect as option (radio) buttons but without VBA. Do you think it is possible?
See attached using radio buttons; could this be used?
You can only do this with VBA. If you want to use this method, copy this code to Sheet1 in your workbook (Press Alt-F11 to get to the editor)
You will also need to change your data validation back to just the =$G$2 list value and save your workbook as an xlsm filePlease Login or Register to view this content.
With radio buttons is OK, but how can I make the link between option chosen and let say points assigned (like in the attached)
Try
=INDEX($D$2:$D$5,F2)
See attached: ensure radio buttons are in sequence.
Thanks all.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks