the following entry staff identity card number in order to explain the operation as an example of concrete steps.

set up to verify the validity of the conditions

The assumption that G as a staff, "ID number" field, G2 cells for the first identity card numbers of staff's cell. Not enter, we can set out the validity of the conditions to ensure that the uniqueness of column data.

Figure 1 input formula

selected, click "Data" menu in the "validity" command, the pop-up "Data Validation" dialog box, select "Settings" tab in the "allow" drop-down list, select the "Since the definition of" in the "formula" box enter "= COUNTIF (G: G, G2) = 1" (all the characters in the formula to use half-angle in English, does not include the double quotes, as shown in Figure 1).

set up warning error message

Set error message for the purpose of warning is to remind users to enter data correctly. Concrete steps are: Click the "Data Validation" dialog box "warning error" tab, in the "Title" box enter "data input error", in the "wrong message" box, type "you have just entered data already exist, check the uniqueness of the data!. " After setting, click the "OK" button (as shown in Figure 2).

Figure 2 Set message

this point, has set up a G2 cell to verify the validity of conditions and the error message. In order to apply this setting out of the G (with the exception of the name field that is the cell where the cell G1), shank tools can be used to fill the formula down to copy it to drag out the other G cells.

Enter the ID card information

Set up after the completion of more than we can out to the G type of the ID number of employees. Enter a per staff ID number, Excel will automatically verify the validity of the data, if the data already exists, the system will prompt a warning box pop-up error, as shown in Figure 3.

Figure 3 box warning

these functions can only verify the uniqueness of the data, the median if the data input error, the system will not detect the error. If the input data needed to verify the number of bits at the same time, ID number or for example, plans could be changed to a formula of "= AND (COUNTIF (G: G, G2) = 1, OR (LEN (G2) = 15, LEN (G2) = 18)) ", Figure II error message changed to" Please check data or input data only the median fault!. " Settings after re-copy the formula cell G2 to G shown in other cells. The meaning of the formula is: G out in the data input must be the only data on the median and must be 15 or 18.

also need to remind you that as a result of G out ID number is entered, the median over the 11-bit data, so the best data in the input between the G out of all the election will be selected, set the "format cells" The "digital Category" format to "text" format, so as to ensure the correct forms of ID number input.