Validations failed when data copy paste in cell

Question

I have done a simple validation in Excel as follows, 

Step 1: Select cell,

Step 2: Go to Data ---> Validations

Step 3: In Settings--------> Allow: List

Step 4: Source: =$A200:$A$207 (Range of list items without blanks)

Step 5: OK.

Now, Its seems that validations done. Now if I type different value than the list it will show me error message.

But, Now when I copy anything by Ctrl+V (which is not in list) and paste in the validated cell. It is not showing any error message.

It means that the cell with validations can take any value by paste option.

How ? And Why?

This is not helpful at all.

Please do reply.

******@gmail.com

Answer 1

Hi,

That indeed is a serious and well documented flaw in data validation and there is no setting to overcome it. This link provides a workaround:-

http://www.j-walk.com/ss/excel/tips/tip98.htm

Answer 2

Hi,

       This is not the solution that this is a flaw. There should be proper solution. If it is not there in Excel for such a simple problem then I am requesting to Microsoft that please remove the Validation functionality from Excel. 

Thanks,

Sujay Chaudhari

Hi,

 

That indeed is a serious and well documented flaw in data validation and there is no setting to overcome it. This link provides a workaround:-

 

http://www.j-walk.com/ss/excel/tips/tip98.htm