XL97: Data Validation Is Only Accurate with Manual Entry (159403)


The information in this article applies to:

  • Microsoft Excel 97 for Windows
This article was previously published under Q159403

SYMPTOMS

In Microsoft Excel 97, cells to which you apply data validation restrictions (using the Validation command on the Data menu) may accept invalid values.

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.

STATUS

Microsoft has confirmed that this is a problem in Microsoft Excel 97.

MORE INFORMATION

The ability to specify what data is valid for either an individual cell or a range of cells is a new feature in Microsoft Excel 97. This feature allows you to do the following:
  • Create a list of the entries that restrict the valid data values.
  • Create a prompt that explains the type of data that is valid for a given cell.
  • Display a message when incorrect data is typed.
  • Check for incorrect entries using the Auditing toolbar.
  • Set a range of numeric values that can be entered into a cell.
  • Determine if an entry is valid based on a calculation in another cell.

REFERENCES

For more information about data validation, click the Index tab in Microsoft Excel Help, type the following text

data validation, restriction options

and then double-click the selected text to go to the "Types of data restrictions" topic.
Modification Type:MajorLast Reviewed:10/22/2000
Keywords:kbprb KB159403
©2002 Microsoft Corporation. All rights reserved.