XL97: How to Use Data Validation to Prevent Duplicate Entries

ID: Q189005

The information in this article applies to:

Microsoft Excel 97 for Windows

SUMMARY

This article explains how to use data validation to prevent you from making duplicate entries in a list.

MORE INFORMATION

The following example uses data validation to prevent duplicate entries from being entered in cell A1 to cell A50. To do this, you need set up two validation rules: one for the first cell in the list, and one for the rest of the cells. To create the example, follow these steps:

1. Save and close any open workbooks, and then create a new workbook.

2. Select cell A1, and then click Validation on the Data menu.

3. In the Allow list, click Custom. Type the following formula in the

   Formula box:

      =ISERROR(MATCH(A1,A2:A50,0))

4. Click the Error Alert tab. In the Title box, type the title of the error

   message. In the Error Message box, type the error message that you want
   the user to see. In the Style list, click Stop. Click OK to apply the
   validation rule to cell A1.

5. Select cells A2:A50, and then click Validation on the Data menu.

6. Click the Settings tab. In the Allow list, click Custom. Type the

   following formula in the Formula box:

      =ISERROR(MATCH(A2,INDIRECT("$A$1:$A$"&ROW()-1),0))

7. Click the Error Alert tab. In the Title box, type the title of the error

   message. In the Error Message box, type the error message that you want
   the user to see. In the Style list, click Stop. Click OK to apply the
   validation rule to cells A2:A50.

As you make entries into cells A1:A50, Excel applies the validation rules that you have established. If you make a duplicate entry, you will receive an error message. Clicking Retry allows you to change the cell entry. Clicking Cancel removes the cell entry.

For additional information about data validation, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q159252
   TITLE     : XL97: Description and Examples of Data Validation

REFERENCES

For more information about data validation, click the Office Assistant, type "data validation," click Search, and then click to view "Specify the valid entries for cells."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q120802
   TITLE     : Office: How to Add/Remove a Single Office
               Program or Component

Additional query words: XL97

Keywords          : kbdta kbdtacode xlui xlformula KbVBA 
Version           : WINDOWS:97
Platform          : WINDOWS
Issue type        : kbhowto

Last Reviewed: January 3, 1999