Using Excel data validation to implement a variety of sorting options for a cell drop-down menu

First, the basic knowledge of preparation

1. Create multiple Options Pull-down Menu

There is a better way to make a drop-down list in Excel cells because the Drop-down list may have 30 or more items, such as 100 entries in data-availability-source, which I remember can only fill 30.

Create more than 30 methods (in 50 cases):

The 50 items selected in the Drop-down list are filled in A1-a50, select Insert-name-definition, the definition name can be filled in the content "level", the definition of the reference location is A1-A50, after the determination of the first level of the Drop-down content into the "data-validity-source" or "data-validity-source" to fill in "= $A $: $A $ ".

2, select a Drop-down menu, with a number of values with the square

The table I'm making is more complex, and I'm going to enter data on one line and it's related to some data, and it's going to be a lot of data.

such as: A1 is a drop-down list, I selected AA, the same line of the AA model, prices appear, and is every line is so, can be achieved? Complex?

Set: The original data table in the Sheet1 table, a column model, b--h listed as relevant data. The new table is built in Sheet2 table, table-type with SHeet1 table. Select the area of column A of the Sheet1 table (set to A2 to A30) and define the name "model".

In the Sheet2 table of the A2 cell, data → validity, "Allow" select "sequence", "source" input "= Model" (equal to be entered in English state), OK exit.

Enter the formula in the B2 cell:

=if ($A 2<>0,vlookup ($A 2,sheet1! $A $: $H $30,column (), 0), "") then pull the B2 cell horizontally to the H2 cell. Then pull the A2 to H2 cells down several lines. After the Model A is selected, the related data appears later.

Two. Drop down menu A variety of sorting options quick Batch Input

Because of the work needs, often to the enterprise's unit name into the Excel table, as required to enter the same business name every time to exactly the same, I used "Data validation" made a Drop-down list to enter. However, because there are more than 150 unit names, the Drop-down list is too long, it is very inconvenient to choose, so I have improved it to achieve the "classification list selection, Fast unified input" purpose.

To use the instance interface:

1, build the library

Start Excel2000 (XP can also), switch to the SHIFT2 worksheet (other worksheets can also), the construction enterprise name according to its qualification level, respectively, enter different columns of the cell, the establishment of a database of enterprise names (Figure 1).

[Special Tip] If another enterprise joins, you can enter the name at any time in the cell below the column.

2, naming

In the SHIFT2 worksheet, select Column A (the column of the first-level enterprise name), and then set the cursor in the Name column (on the left side of the formula bar, as shown in Figure 2), enter "level" and press "enter" to confirm.

Imitate the above operation, the B, C, D column is named "Two, three, four" respectively.

3. Establish

Switch to the SHEET1 worksheet, select the column (such as column C) where you want to enter the enterprise qualification level, and execute the data → effectivity command to open the Data Validation dialog box (Figure 3). Go to the Settings tab, click the Drop-down button to the right of allow, select the sequence option, and, in the Source box below, enter the "level, level two, level three, four" sequence, and after you enter it, make sure you exit.

[Special Tip] When you enter a sequence, separate the elements in the sequence with commas in the English state.

Then select the column that you want to enter the enterprise name (for example, column D), open the Data Validation dialog box again, select the Sequence option, and then enter the formula in the Source box: =indirect (C1), OK exit.

4. Use

Select the C column of any cell (such as C4), a Drop-down button appears on its right, click this button, you can choose the Enterprise qualification level to fill in the corresponding cells. Then select the cell in the D column (Figure 4), and also a Drop-down button on the right, click this button to select the desired enterprise name from the Enterprise Name list of the appropriate qualification level to fill in the cell.

Three. Another way to quickly bulk-enter the drop down menu option category

There are a number of provincial and municipal data: Provinces and provinces under the jurisdiction of the city list, the need to achieve multi-level linkage drop-down menu operation, the specific mode of operation as follows:

First step: Define the name of each range of cells

Select the range of cells in the A1:A5, and then the formula--created based on the selection--like the diagram setting

PS: Repeat for B, C, D, and E columns respectively

Step two: Set the options for the province column

Select the range of g1:g22 cells, and then data--data validation

pops up the Data Validation Settings window, and then as shown

Where the source is "= Province", where the province is relative to the previously defined cell range name

Province option done

Step three: Set the options for the City column:

Select the range of h1:h22 cells, and then data--data validation

In the pop-up window, set the following: the source =indirect ($G 1),

Ps:indirect function that returns the reference specified by the text string.

The city option is also done, n-level operation, etc.