How to create cascading menu in excel?

How to create a multi-level drop-down menu in excel? ?

You need to use VBA to monitor table content changes. The implementation method is as follows:

1. First enter two columns in the areas that will definitely not be used in sheet1, corresponding to "service attitude, business capabilities"

Name the area of ​​"Voice and Intonation, Overtones, and Active Service Awareness" as "Service Attitude"

Name the "required conversion, query error" area as "Business Capability"

2. Then enter the following code on the sheet1 code page, which is used to monitor changes in A1 and adjust B1 settings based on the changes.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range(＂A1＂) Then

Range(＂B1＂).Validation.Delete

Range(＂B1＂).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:=＂=＂ & Target.Text

End If

End Sub

How to set up a two-level drop-down menu in Excel

First look at the original data. The original information is in a worksheet. The first row is the name of the province and city. The following actions correspond to the place names and district names under the province and city. You need to create a linked secondary drop-down menu in columns A and B in another worksheet.

2

First, select all the data in the original table (including extra blank cells), and press F5 or Ctrl G to bring up the positioning dialog box. Select [Target Criteria] in the lower left corner.

3

As shown below, select [Constant] and click the [OK] button. In this way, all non-empty cells are selected.

4

Select [Data]-[Validity]-[Create based on selected content] in the ribbon.

5

Since the title is on the first line, select [First Line] as the name, and then click the [OK] button.

6

After the operation is completed, you can see the defined name in the name manager.

7

Select the name of the province and city in the first row (also locate the non-blank cell), enter the two words "province and city" in the name box, and then press Enter, thus defining a name of "province and city" .

8

Select cell A2 on the operation interface and select [Data]-[Data Validity].

9

As shown below, select [Sequence], enter [Source]: =Province and City, and then click the [OK] button.

10

In this way, a drop-down menu of province and city information is generated in cell A2.

11

In the same way, select cell B2, set data validity, and enter the formula: =INDIRECT($A$2).

12

After the setting is completed, when "Hebei" is selected in cell A2, the drop-down menu of B2 returns the information of "Hebei"; when "Beijing" is selected in cell A2, the drop-down menu of B2 returns the information of "Beijing".

13

Notice:

The formula for the above-mentioned secondary drop-down menu settings uses absolute references for both rows and columns. If you want to make the secondary drop-down menu available to the entire column, change the formula to: =INDIRECT($A2).

How to make multi-level linkage drop-down menu in Excel

Take WPS 2019 version as an example:

Regarding how to set up multi-level drop-down items in excel tables, the operation method in WPS "Table (Excel)" is as follows:

1. First, we enter the data into Sheet2 and Sheet3 respectively in the form. Sheet2 contains the first-level and second-level data, and Sheet3 contains the second-level and third-level data. Similar to the way we made the secondary drop-down menu before, we first enter Sheet2, select all the data, click "Formula-Specify-Only keep the check in front of "First Row", and cancel all others. In the same way, we enter Sheet3 again to operate;

2. Set up a first-level drop-down menu: Enter Sheet1, select cell A2, enter "Data-Validity-Validity-Select Sequence", and select the "A1:C1" cells in Sheet2 in "Source" ( It’s the content of the first-level drop-down menu);

(Note: After completing the setting, select an option first, otherwise an error will be prompted when setting the second level)

3. Set up a secondary drop-down menu. Position the cursor to cell B2, then enter "Data-Validity-Validity-Select Sequence" and "Source" to enter "=INDIRECT(A2)" to confirm;

4. Set up a three-level drop-down menu. Select cell C2 and perform the same operation, except that you enter "=INDIRECT(B2)" in "Source". Finally, we select cells A2:C2 and fill them downward. At this point, our multi-level drop-down menu is complete.

How to use Excel data validity to create a multi-level linkage drop-down list

Method/Step

Get to know the drop-down menu

As shown in the example below, the first-level drop-down menu is for provinces, the second-level is for cities, and the third-level is for counties or districts. The second-level drop-down menu needs to automatically select the corresponding city based on the selection on the first-level menu. Similarly, the third-level drop-down menu needs to automatically select the corresponding county or district based on the selection on the second-level menu.

Create a first-level drop-down menu

The first-level menu is province, that is, Guangdong and Guangxi, so you can directly use the reference of data validity.

Create a second-level drop-down menu

The second-level menu is city. The second-level menu needs to display the second-level menu content according to the selection of the first-level menu. For example, if Guangdong is selected at the first level, the menu that needs to be selected at the second level is "Guangzhou, Dongguan, Shenzhen...";

Creating a third-level drop-down menu

The third-level menu is county. The third-level menu needs to display the third-level menu content according to the selection of the second-level menu. For example, if Guangzhou is selected at the second level, the menu that needs to be selected at the third level is "Tianhe". district.....";

5

Remove spaces in data validation menu

When defining a name, you need to select the range in which the name is defined. If the range includes spaces, spaces will appear in the menu. The simplest way is to only select the range with data to define the name.

The above is the detailed content of How to create cascading menu in excel?. For more information, please follow other related articles on the PHP Chinese website!