Office hacks: refusing to make mistakes to create Excel multi-level linked drop-down list

A street often has many communities under its jurisdiction, and each community contains several subdivisions, so it is necessary to enter the names of communities and subdivisions when entering data. Since there is no standardized data name, different statisticians will enter different names for the same community, for example, in the following table, the community “Yansha – after (East Run Maple View)” is recorded as “Yansha” by some people, while others are recorded as ” East Run Maple View” (Figure 1). Such data to the later aggregation, categorization brings great inconvenience, now we can use Excel (this article to the 2016 version for example) function to create a multi-level linkage menu, so that users only need to selectively input can ensure the uniformity of data field names.


Figure 1 Example data

From the above data, we can see that there are mainly three levels of addresses, namely, “street office”, “community” and “neighborhood”, and each level contains different numbers of subordinates. To achieve selective data input, here we have to correspond to the different levels of data respectively. For example, in the selection of Luoxing street Xiangli community, select the list is the contents of column B, the effect is similar to our usual online shopping address selection.

First establish the first level of data, where the first level of data is the name of the street office. Create a new worksheet, as prompted in cell F2 and F3 to enter the name of the street office, then positioned in cell A2, click “Data → Data Validation”, in the “Allow” item select “Serial “, select “=$F$2:$F$3” at the source, the A2 cell drop down to fill (Figure 2).


Figure 2 First-level data validation

This way column A data input can only be selected from cells F2:F3, which is the content of the first level menu. If you want to add other content, just add content to the sequence (Figure 3).


Figure 3 Selective input of primary menu content after data validation

The next step is to set up the secondary menu, which corresponds to each community. Because each street office is under the jurisdiction of different communities, so the secondary menu should correspond to the corresponding primary menu. Secondary menu settings can use the INDIRECT function for dynamic reference.

Positioned to the cell G5 and H5, respectively, enter the “Luoxing Street Office” and “Jiamei Street Office”, in order to facilitate the reference, the name entered here must be consistent with the name of the first-level menu. Select G2: H5 area, switch to the menu bar click “Formula → Name Manager → according to the selected content to create”, in the pop-up window check the “first line”, respectively, to create the name “Roxing Street Office ” and “Jiamei Street Office” of the two new names (Figure 4).


Figure 4 Create name

It should be noted here that because each level menu (street office) may contain a different number of subordinate menus, for example, in the above example, the Luoxing street office has 3 communities under its jurisdiction, while another street office has only 2, so we also need to set it in the name manager. Open the Name Manager, select “Jiamei Street Office”, change the reference location to “=Sheet2!$H$3:$H$4”, because its parent Jiamei Street Office only has jurisdiction over two communities (Figure 5).


Figure 5 Edit the name

Position to cell B2, the same as above to open the data validation settings, “allow” item select “series”, enter “=INDIRECT($A2)” at the source, here B2 cell input using the INDIRECT function for reference (Figure 6).


Figure 6 INDIRECT function settings

In the INDIRECT function, here “($A2)” is the relative reference to the line. Indicates that the input in cell B2 is a reference to the contents of A2, so that in A2 (first-level menu) to select different content, the sequence of B2 will appear corresponding to the contents of the second-level menu, so as to achieve dynamic reference, drop-down fill by prompt (Figure 7).


Figure 7 dynamic reference level menu

The three-level menu settings are similar, first in the I2: M2 cells in turn, enter “fragrant pear community, gravel community, Huang Shuang community, Huangshan community, and community”, and then the same as above according to the content of the creation of the name, the source of data validation, enter “=INDIRECT ($B2) “, so that the input of cell C2 use the INDIRECT function to dynamically reference the contents of B2 for input. Now we choose a different community in B2, C2 will synchronize the corresponding community under the cell name (Figure 8).


Figure 8 Dynamic reference secondary menu

In the future, when entering the name of the statistical table, data entry can only be done in the drop-down list to select the pre-prepared standard data, thus effectively ensuring the uniformity of data. For the sake of table simplicity, you can also select F1:M18 data, right-click and select “Hide” to hide it, or directly enter the pre-prepared data in another worksheet, and set the worksheet to “Read Only”, “Hide”, which can be more convenient. “Hide”, so that it can be more convenient for data entry operations (Figure 9). Similarly, four levels, five levels (or even more levels) of the menu settings can be in accordance with the above method, for the need to dynamically refer to the previous level of menu input, as long as the previous level of menu content to create the corresponding name, and then finally use INDIRECT to refer to.


Figure 9 Final entry screen


Leave a Comment