Self-made Excel drop-down menu quick batch Input

When using the import function, it is necessary to provide users with an import template.
When creating an imported excel template, there is often a need to restrict user input. A drop-down box is a good form.

 

Because of work requirements, the name of the construction company is often entered into an Excel table, because the names of the same company must be entered each time, I used "Data Validity" to create a drop-down list for input.
However, since there are more than 150 organization names, the drop-down list is too long and it is inconvenient to choose from, so I improved it, achieve the goal of selecting a category list and quickly unified input. Download Sample Files
Use the instance interface:

1. database creation
Start excel2000 (XP), switch to the shift2 Worksheet (other worksheets are also available), and enter the names of the construction enterprises in cells of different columns according to their qualification levels, create an enterprise name database (1 ).

[Note] if other enterprises are added, you can enter the names in the cells in the column at any time.
2. Name
In the shift2 worksheet, select column A (the column where the first-level enterprise name is located) and place the light in the name column (on the left side of the edit column, 2 ), enter "Level 1" and press "enter" to confirm.

Follow the preceding steps to name columns B, C, and D as "level 2, level 3, and Level 4 ".

 

3. Create
Switch to the sheet 1 worksheet, select the columns (such as column C) that need to enter the enterprise qualification level, execute the "Data → validity" command, and open the "Data Validity" dialog box (3 ). Go to the "Settings" tab, click the drop-down button on the right of "allow", select the "sequence" option, and enter "Level 1, level 2, and level 3" in the "Source" box that appears below, level 4 sequence. After the input is complete, confirm to exit.

[Note] when entering a "sequence", use commas to separate the elements in the sequence.
Select the columns (such as column D) for which you want to enter the enterprise name. In the "Data Validity" dialog box, select the "sequence" option and enter the formula in the "Source" box: = indirect (C1). Confirm to exit.
4. Use
Select any cell (such as C4) in Column C, and a drop-down button appears on the right. Click this button to select the enterprise qualification level and fill in the corresponding cells. Then, select cell (4) in column D corresponding to the cell, and a drop-down button appears on the right of the cell. Click this button, you can select the desired enterprise name from the Enterprise Name List of the corresponding qualification level and fill in the cell.

How is it? Enter the company name in this way (of course, this method is not only applicable to entering the company name). Is it fast and unified ?!