How to achieve the effect of multi level linkage drop down menu

1. Excel first-level drop-down menu production.

For example, to make a drop-down menu in cell A1:A10, the steps are as follows:

Second, excel secondary drop-down menu production.

For example, to make a secondary linkage drop-down list menu in A1:A10 and B1:B10 cells, the steps are as follows:

The specific steps are as follows:

1. Select the original data, press the shortcut key Ctrl+G, bring up the positioning dialog box, click the positioning condition, select the constant, and check the items in the box above, and then click OK.

2. Switch to the "Formula" tab in the menu bar, click "Create from Selection", select "First Row" here, and then you will see the following data in the "Name Manager".

3. In the result area, that is, under the province, check the blank slot, and the number of boxes should be more than the number of columns of the original data, then select the "Data" tab, and click "Data Verification".

4. In the Settings tab, the verification condition allows you to select "Series", and select the first row of the source data in the ** area box, that is, the province, click OK, and you can see the drop-down selection of the province that has been generated.

5. In the same way, under the city, select the blank space, and the number of boxes should be more than the number of columns of the original data, and then select the "Data" tab and click "Data Verification".

6. In the settings tab, the verification condition allows you to select "Sequence", enter =indirect($d 2) in the ** area, where d2 changes due to the location of your **, that is, the position of the province here, click OK.

7. According to the above method, you can make the Excel secondary linkage drop-down menu.

The steps for "drop-down menu to implement multi-level linkage in excel" are as follows:

1. Take Excel 2016 as an example to open the excel sheet;

2. Create a table in the F:H column with the first-level menu as the title and the second-level menu arranged vertically;

3. Select the first-level menu setting area, in the "Data Validation" ("Data Validity" in the "Data Validity" version below the "Data 2007" option), select "Sequence" for the "Settings" option, select F1:H1 cell, OK;

4. Select the F:H column, select "Create from Selection" under the "Formula" option, check "First Row" in the "Create Name for Selected Area" dialog box, and press "OK" to complete the creation of the name;

5. Select the secondary menu setting area, in the "Data Validity" under the "Data" option, select "Sequence" for the "Settings" option, **Input=indirect(A2) OK;

6. In the first-level menu setting area, click the drop-down option button to select the corresponding menu content;

7. In the secondary menu setting area, click the option button, and the secondary menu corresponding to the primary menu will pop up for selection.

Drop-down menu method for creating multi-level linkage:

1. For example, first of all:

The department C2:C4 of Company A is defined as "Company A", and the employees of the General Office of Company A are defined as "General Office" by E2:E3. For the rest, define the name in the same way.

2. In column A20, add a drop-down menu through the "Series" of data validity:

3. In column B20, type =indirect($a$20):

It means that the content in b20 is the area corresponding to the name "a20" (indicating the value in a20). Here, if A20 is Company A, then B20=Indirect($a$20) means that the name in the definition name is C2:C4 as defined by A20.

direct means that the content in parentheses after it is converted from the defined "name" to the "region" it represents.

4. In column C20, type =indirect($b$20):

Take the question as the content (the subject of the email must contain "excel", I will use this as a basis to identify non-spam emails, so as not to delete them by mistake), and send the excel sample file (showing the current situation and target effect) as an attachment to read it.

Let's start with a drop-down menu that doesn't linkage:

Let's open the office2007 data data validity 2 in the pop-up dialog box and click "Sequence" in the "Allow" drop-down menu

1. Manually enter the sequence name and separate it with a comma under pinyin.

2. Select a reference.

4. Use the first manual method of entering the sequence name to make a drop-down menu.

Step to read. 5. Use the second selection method to make a menu.

Make a data table for the drop-down menu.

Open Excel and make a data table for department and name.

2Create a targeting.

Select the department and name area in the data table, use the shortcut key Ctrl+G to open the location dialog box, and click Positioning Condition.

In the Targeting dialog box that appears, select a constant and click OK. Multi-Figure 3

Create a formula. In the Formula tab, select Create from Selection, select First Line in the dialog box that appears, and OK.

4. Create a new sheet.

Add a new sheet sheet, enter the department in the new sheet A1 cell and the name in the B1 cell.

5. Make a first-level drop-down menu.

Select the cell below the Department column, and then perform Data - Data Validation.

In the Data Validation dialog box, in the Settings tab, click the drop-down button to the right of Allow, and select Sequence.

Then click the button on the right side of the "**" box, select the first row "Department" in the data table made earlier, and complete the production of the first-level drop-down menu. Multi-Figure 6

See the effect of the first-level drop-down menu.

At this time, the production of the first-level drop-down menu has been completed. Let's take a look at the effect first. A second-level drop-down menu is then created.

7. Make a secondary drop-down menu.

Select the cell below the Name column to perform Data – Data Validation. In the Data Validation dialog box, in the Settings tab, click the drop-down button to the right of Allow, and select Sequence.

8. Check the effect of the secondary drop-down menu.

Click the drop-down menu in the Department column, select the department name, and then click the drop-down menu in the Name column to select the person in the department.

Set the secondary drop-down menu description:

1. Define the name:

1) Enter the first-level menu content in the table in advance in each cell, select the first-level menu content in the box, and enter any name such as "first-level" in the "name box" in the upper left corner, that is, define its name.

2) In the same way, define the secondary menu, if there are multiple secondary menus, define them separately, such as: Henan, Hebei, Shanxi, Shaanxi" is defined as "North China"; Hunan, Hubei, and Jiangxi, defined as "Central China".

2. Set a drop-down list:

3. Set the secondary drop-down list:

indirect($a 1) OK to complete the secondary drop-down list settings. The A1 cell is an example, that is, the first-level drop-down list cell corresponding to the second-level drop-down list.

4. Use of two-level drop-down lists:

Select the first-level drop-down list area to enter the first-level menu, that is, one of North China and Central China, such as "North China", and then select one of "Henan, Hebei, Shanxi, Shaanxi" in the corresponding cell in the second-level drop-down list area.

Use Excel data validity to create multi-level dynamic drop-down lists.

Web Links.

Make an Excel drop-down menu for secondary linkage.

1. Organize data sources.

2. Define the name.

3. Level 1 menu setting. Click Data – Data Validation, which allows the condition to select the sequence, and select the first row of the data region, which is the area with the name to be defined.

4. Secondary menu settings.

First of all, let's take a look at the raw data, the original information is in a worksheet, the first row is the name of the province and city, and the following several behaviors correspond to the place name and district name under the province and city. You need to create a secondary drop-down menu for column A and column B in another worksheet.

First, select all the data of the original table (including the extra blank cells) and press F5 or Ctrl+G to bring up the location dialog box. Select Targeting in the lower-left corner.

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

Select Data - Validity - Create from Selection on the ribbon.

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

Once you're done, you'll be able to see the name of the definition in the Name Manager.

Select the name of the province and city in the first row (also locate the non-blank cell), enter the word "province and city" in the name box, and press enter, so that a name of "province and city" is defined.

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

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

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

After the setting, when the A2 cell selects "Hebei", 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".

Note: The formula set by the above secondary drop-down menu takes the absolute reference of the row and column, if you want to make the second-level drop-down menu available for the whole column, change the formula to: =indirect($a 2).

1. Select the column where the first-level drop-down option is located, in the "Data Validity" under the "Data" option, select "Series" as the allowed type in "Settings", and select the content of the first-level option in Data**;

2. Select the corresponding table in the menu, in the "Defined Name" of the "Formula" option, "Create from Selection", and select "First Column", OK, to complete the creation of the name;

3. Select the secondary menu setting column, in the "Data Validity" under the "Data" option, "Settings" allows the type to select "Series", and the data ** input = indirect(a1) OK;

4. After completing the following settings, when the first-level options are generated, the corresponding second-level linkage options will be generated in the following columns.

**Make drop-down menu input data, it is very efficient to improve work efficiency, and you can also make a second-level linkage drop-down menu, that is, after the first-level menu is selected, the second-level menu only displays the content associated with the first-level menu for us to choose.

With the data cells selected, Ctrl+G (or F5) is set to Constant (Effect: Selected Non-Empty Cells).

2.Formula – Name of the definition – created from the selection – first row or leftmost column 3Level 1 Menu: Data Validation – Sequences.

4.Secondary menu: Data Validation - Sequence - Formula: =indirect (the cell where the primary menu is located).

If you don't understand, please contact me!!

Let's start with a drop-down menu that doesn't linkage: let's play.

1. In the pop-up dialog box, click "Allow" to drop down the dish.

2. There are the following two in the "**" under "Sequence".

3. Use the first manual method of entering the sequence name to make the drop-down dish.

4. Use the second option to quote the next menu.

5 We click on "Formula" "Define Name":

6 in the pop-up window"Name"Type what we want to define.

First of all, select the basic data in **, press [Ctrl+G] on the keyboard, and the following positioning window will pop up.

2.After that, click [Targeting Conditions] below, select [Constant] and click OK. After this Hu Qingji operation, we only selected the cells for which we had data.