I have 2 columns A and B into which users must enter their data. In column A the responses allowed are "yes", "no" and in column B the responses allowed are "once a week", "twice a week". I have created drop down boxes which users click on in order to enter their data.

The problem is that some users in the past have entered data correctly into A, and then somehow accidentally copied and pasted the contents of the cells in A across into B, rather than entering data into B manually using the drop down boxes. So the values they entered in B are then "yes", "no" which they shouldnt be. This causes later computations to be wrong.

I need to allow them to enter data into both A and B, but they must be the correct values for that column. They must not be able to copy the contents from A to B, making B incorrect.

I dont seem to be able to allow users to enter data, but make sure they enter only data in the range allowed. I have tried locking cells, but this doesnt work. I can either lock cells in which case they cant enter any data at all or unlock cells in which case they can corrupt them.