Skillfully borrow "name", in the WPS table to establish a multi-level Drop-down list

In the WPS table in order to facilitate the specification of data entry, we often in the corresponding cell to provide a drop-down list to select input. But what if you need to set up a multilevel Drop-down list?

In a school with a high, senior high, three a total of three levels, each level of the department has 10 or so classes. In the past, when statistics students related information, often found that the class, the level of the table submitted by the very irregular, so for the sake of specification, it is necessary to establish multi-level Drop-down list, as shown in Figure 1.

Figure 1

For this reason, we should use "name" to achieve the goal.

First, define the name

As shown in Figure 2.

Figure 2

Create the diagram table in the SHEET1 worksheet. Each column heading is indicated by the corresponding level department name, under it respectively enters each corresponding class, four level department respectively in a-c column. In the E1 cell input "level department", its below input four level department name. Note that the name of the level section should be consistent with the column headings.

Select the cell in column A, and click the menu command "Insert → name → definition" To open the Define Name dialog box. Enter "Freshman" in the upper input box and confirm that the data in the reference position input box below is "=sheet1! $A: $A", as shown in Figure 3.

Figure 3

After you click OK, you can define a column cell range as "tall". No, you can enter in the form of the name box "tall", carriage return, is not the entire a column was selected?

You can use the same method to select the columns B, C, and e column cells, respectively, in the Define Names dialog box, and define them as "sophomore", "senior three", and "level" names. You can also enter a name directly in the upper input box, modify the reference position below to the appropriate range of cells, and then click the Add button to add the name to the list of names in the current workbook. For example, when defining a "senior three" name, its reference position should be changed to "=sheet1! $C: $C", when defining a "level" name, its reference position should be changed to "=sheet1! $E: $E."

Ii. Setting Data availability

The table shown in Figure 4.

Figure 4

Select C2 and the following range of cells, click the menu command "data → effectivity" to open the Data Validation dialog box. Select sequence in the Allow Drop-down list, and then enter the = Level section in the Source input box below, and the other settings are shown in Figure 5.

Figure 5

Triple Recommendation: Click for free Download the latest version of WPS Office software "to see more computer tutorials

When you are sure, position the mouse over the C2 or its lower cell, and you can find a Drop-down button on the right, and click to appear in the list of level names listed in the SHEET1 Worksheet e column.

The key is column D in the table, how do I get a class list at the corresponding level in column D based on the name of column C? In fact, this step is also a window paper nature, a stab on the broken. Select D2 and the following range of cells, in the Data Validation dialog box, the Allow list still selects sequence, but enter the formula "=indirect ($C 2)" In the source input box, as shown in Figure 6.

Figure 6

When you are sure to click the corresponding cell, you can see the effect shown in Figure 1.

The function indirect ($C 2) is the value of the data returned in the C2 cell. For example, the result of "=indirect ($C 2)" in Figure 1 of this example is "= Tall", and the result of "=indirect ($D 2)" is "= Senior three".

If you have more levels of drop-down lists, there is no need to worry, that is two steps, first define the name, and then in the Data Validation dialog box to refer to the name.