Application of EXCEL drop-down menu Sequence

As the business often uses EXCEL to import data, it is unavoidable to create an EXCEL template.
Below is an article about the pull-down menu made on the Internet, which is very good and solves a big problem.

[Application 1] Implementation of drop-down menu Input

Example 1: Direct custom Sequence
Sometimes we enter the same number of values in each row of each column. For example, when entering the student level, we only enter four values: Excellent, good, qualified, and unqualified. We hope that the Excel2000 cell can be selected in the drop-down box as in the drop-down box.
Operation Procedure: first select the row or column to implement the effect, and then click "data \ validity" to open the "Data Validity" dialog box. Select the "Settings" tab, in the "allow" drop-down menu, select "sequence". In "Data Source", enter "Excellent, good, qualified, unqualified "(Be sure to separate them with commas !); Select the check boxes "ignore null values" and "provide drop-down menu. Click the "input information" tab and select "display input information for selected cells". In "input information", enter "select here ".

Example 2: use the data in the table as the sequence source.
Sometimes there are many sequence values, and the sequence is defined directly outside the printing area of the table, and then referenced.
Operation Procedure: first define a sequence outside the printed area of the same Worksheet (assuming it is in Z1: Z8), such as "single-parent families, families with disabilities, students with disabilities, special difficulties, low income, emergencies, orphans, military martyrs, and so on. Then select the columns (funding reasons) to achieve the effect, and click "data \ validity" to open the "Data Validity" dialog box; select the "Settings" tab, select "sequence" from the "allow" drop-down menu, click the expand button on the right (with a red arrow) in the "Source" column, and drag the scroll bar with the mouse, select the sequence area Z1: Z8 (if you remember, you can directly enter the = $ Z $1: $ Z $8; select the check boxes "ignore null values" and "provide drop-down menu. Click the "input information" tab and select "display input information for selected cells". In "input information", enter "select here ".

Example 3: Create a drop-down menu across two worksheets
Use the INDIRECT function to implement cross-Worksheet

In Example 2, select the source to replace the input =z Z $1: $ Z $8 with = INDIRECT ("Table 2! $ Z $1: $ Z $8 "), you can create a drop-down menu across two worksheets.

[Application 2] automatic conversion of input methods between Chinese and English

Sometimes, we need to enter Chinese characters and English letters between different rows or columns. We hope that Excel can automatically convert the input method between Chinese and English.
Procedure: Enter the Chinese name of the student in column A and the English name of the student in column B. Select column B and click "data \ validity" to open the "Data Validity" dialog box. Select "input method" and select "close (English mode)" from the "Mode" drop-down menu) "; then," OK "to see how it works.

[Application 3] data Uniqueness Test

The employee ID number should be unique. To prevent repeated input, we will use "Data Validity" to prompt you.

Operation Procedure: select the cell area (such as the B2 to B14 columns) for which the ID card number is to be entered, run the "Data → validity" command to open the "Data Validity" dialog box, under the "Settings" tab, click the drop-down button on the right of "allow". In the shortcut menu that appears, select the "Custom" option, then, enter the formula = COUNTIF (B: B, B2) = 1 in the "formula" box below to confirm the return. When a duplicate ID card number is entered in the above cells, the system will pop up a prompt dialog box and refuse to accept the entered number.