XL97: Data Validation Is Only Accurate with Manual Entry

CAUSE

This behavior occurs when any of the following conditions is true:

• You select a range of cells in which one of the cells has data validation restrictions. You then type an invalid data value, and press CTRL+ENTER. Excel inserts the invalid value that you typed into all the selected cells. -or-
  
  
• You programmatically insert an invalid value into a cell in which you have applied data validation restrictions. -or-
  
  
• You copy an invalid value from a cell and paste it into a cell in which you have applied data validation restrictions. -or-
  
  
• You drag an invalid value from a cell to a cell in which you have applied data validation restrictions. -or-
  
  
• You use the AutoFill feature to fill an invalid value into a cell in which you have applied data validation restrictions. -or-
  
  
• You are using a Data Form.

NOTE: When you apply data validation restrictions on a cell, Excel only validates the data when you manually type the value in the cell. If you autofill, copy, or drag an invalid value to a cell with data validation restrictions, the data validation restrictions are removed from the cell.