Data Validation - Copypaste special problem
 

I have set validation on one column in an Excel sheet (validation from 'List').

If the user types in a value that is not on the List, then Excel returns the
message box (as it should) telling the user that this value is not allowed.

However, if a user copies a value from another cell and does a 'paste
special value' into the data validation cell, Excel will allow this (i.e.,
will allow the value to remain in the cell even though it is not on the list
of allowed values, and Excel will return no message box telling the user that
this value is not allowed).

Am I missing something?

Thanks in advance for your help.

bill

Data Validation - Copypaste special problem
 

Data validation only tests information that is typed in the cell, not
values that are pasted, even if Paste Special, Values is used.

For that level of validation, you could use programming. Or, add
formulas to other cells that display an error message if invalid entries
are made.

WCM wrote:
I have set validation on one column in an Excel sheet (validation from 'List').

If the user types in a value that is not on the List, then Excel returns the
message box (as it should) telling the user that this value is not allowed.

However, if a user copies a value from another cell and does a 'paste
special value' into the data validation cell, Excel will allow this (i.e.,
will allow the value to remain in the cell even though it is not on the list
of allowed values, and Excel will return no message box telling the user that
this value is not allowed).

Am I missing something?

Thanks in advance for your help.

bill



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Data Validation - Copypaste special problem
 

Debra, thank you. Now I know what I need to do - a little more work (but
fun work). Thanks again.

"Debra Dalgleish" wrote:

Data validation only tests information that is typed in the cell, not
values that are pasted, even if Paste Special, Values is used.

For that level of validation, you could use programming. Or, add
formulas to other cells that display an error message if invalid entries
are made.

WCM wrote:
I have set validation on one column in an Excel sheet (validation from 'List').

If the user types in a value that is not on the List, then Excel returns the
message box (as it should) telling the user that this value is not allowed.

However, if a user copies a value from another cell and does a 'paste
special value' into the data validation cell, Excel will allow this (i.e.,
will allow the value to remain in the cell even though it is not on the list
of allowed values, and Excel will return no message box telling the user that
this value is not allowed).

Am I missing something?

Thanks in advance for your help.

bill



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html