神马文学网Preventing Duplicates On Entry
来源：百度文库 编辑：神马文学网 时间：2024/06/03 05:46:35
In Excel97 and later versions, you can use the Data Validation feature to prevent a user from entering a number (or text) in a cell, if that number already exists in a list. This feature is not available in versions of Excel before Excel97.
For example, suppose your worksheet will contain a list of ID numbers for employees, in cells A1:A50, and you want to prevent the user from entering duplicate numbers in the list. First, select the range A1:A50. Then, go to the Data menu, and select the Validation menu item. This will display the Data Validation dialog. From the "Allow" drop down box, select Custom, and enter the following formula in the Formula box:
=COUNTIF($A$1:$A$50,A1)=1
Then, select the "Error Alert" tab, and enter the message you want to display when the user enters a duplicate value. Finally, press OK. You must use absolute references in the range, and relative references in the criteria, as shown in the example.
Remember, that Data Validation is in effect only when the cell value is changed by the user. It does not catch changes made by VBA procedures, or as a result of a calculation in a formula. Also, Data Validation does not apply to changes made when the user pastes data in to the range.
Other Excel formulas and VBA procedures for working with duplicate and unique entries in lists can be found on the following pages:
Preventing Duplicates On Entry
Duplicates
Deleting Rows
For example, suppose your worksheet will contain a list of ID numbers for employees, in cells A1:A50, and you want to prevent the user from entering duplicate numbers in the list. First, select the range A1:A50. Then, go to the Data menu, and select the Validation menu item. This will display the Data Validation dialog. From the "Allow" drop down box, select Custom, and enter the following formula in the Formula box:
=COUNTIF($A$1:$A$50,A1)=1
Then, select the "Error Alert" tab, and enter the message you want to display when the user enters a duplicate value. Finally, press OK. You must use absolute references in the range, and relative references in the criteria, as shown in the example.
Remember, that Data Validation is in effect only when the cell value is changed by the user. It does not catch changes made by VBA procedures, or as a result of a calculation in a formula. Also, Data Validation does not apply to changes made when the user pastes data in to the range.
Other Excel formulas and VBA procedures for working with duplicate and unique entries in lists can be found on the following pages:
Preventing Duplicates On Entry
Duplicates
Deleting Rows
Preventing Duplicates On Entry
Efficient Web Form Entry on PDAs
ASEAN hails entry into force of treaty on nuke-free zone in Central Asia
Main Entry: go out
Preventing SQL Injection in Java
[Laskey99] Chapter 4. Preventing Data Loss
Value of Genetic Testing for Preventing Blood...
Wikipedia Entry | Story of Santa Claus
China‘s WTO entry recasts economic landscape
Encyclopedia entry: Exploit:HTML/IframeRef.ge...
The Hurry-Up Guide to Preventing Aches and Pains
姚笛的贼船 - Entry for October 16, 2005 神六有多热
Toshiba and Samsung said to be planning netbook market entry
入市信号(Entry)4-交易系统-系统交易方法学习网
On Intelligence
on??university
Come on!!!
depend on 与dependent on的区别
Scott on Writing
Excerpt from On Intelligence
CNN on Nov. 3
什么是Ruby on Rails
PKM bases on web
Lord Palmerston on Programming