Detailed steps for creating a three-level linkage drop-down menu in Excel

How to create a complete three-level linkage drop-down menu in Excel? PHP editor Xiaoxin brings detailed steps! This article will provide an in-depth introduction on how to use Excel's INDIRECT function, OFFSET function, and COUNTIF function to seamlessly connect three levels of data to realize an interactive and dynamic three-level linkage drop-down menu, helping you effectively manage complex multi-level data structures.

1. Prepare data

First write all levels of menus and their contents in Execl for later use

2. Create a single-level drop-down menu

Press ctrl F3 to open the [Name Manager], create a new field named [Project Type]

Click the [Table Icon] to the right of [Reference Location], Select the value in the [Project Type] field (equivalent to the selection item in the menu), and then [OK]

If you want to create a drop-down menu in the cell in column C, row 3, Then first select the cell in row 3 of column C

Open [Data]->[Data Validity], in Excel2010 it is [Data]->[Data Validation]

Select [Allow] value as [Sequence], [Source] value as [=Project Type], and then [OK]. At this time, [Source] refers to the file named just created. The value corresponding to the field of [Project Type].

Click on the cell in column C, row 3, and a drop-down arrow will appear. The options include [Tower Project] and [Room Classification Project] included in the [Project Type] field. , the single-level drop-down menu is now created

3. Multi-level drop-down menu

Create a drop-down menu that links the project type and the construction method. The above has been created [Project Type] drop-down menu, there are two options in the project type: [Tower Project] and [Room Classification Project]. Different project types have different construction methods. Below we create names named [Tower Project] and [Room Classification Project] 】The field

created the [Project Type] drop-down menu in the cell in column C, row 3. In order to achieve the linkage effect, we created the [project type] in the cell in column D, row 3. Construction method] drop-down menu, first select the cell in row 3 of column D, then turn on data validity, and set the value as shown below:

Note: The [source] value here is [ =INDIRECT($C3)], indicating that the value in the [Construction Method] drop-down menu is the value corresponding to the [Field] with the same name as the value in the [C3] cell

Key points of menu linkage: The [domain name] of the second-level menu should correspond one-to-one with the selection items in the first-level menu, so that when the first-level menu selects a different selection, the second-level menu will look for the [domain name] with the same name. Field], and assign the value in [Field] to the drop-down menu. The third-level menu linkage is the same as the second-level menu linkage

4. Multi-line and multi-level menu linkage

To achieve multi-line and multi-level menu linkage, first create a multi-line multi-level menu linkage Level menu linkage, and then drag down to achieve multi-line and multi-level menu linkage.

Note:

In order to achieve multi-line and multi-level menu linkage, there is one thing to pay attention to when setting the source of sub-levels (secondary, third-level...), the second-level drop-down menu The value in [Construction Method] (cell D3) changes according to the change of the first-level drop-down menu [Project Type] (C3). The source must be written as [=INDIRECT($C3)]. If cell E3 creates a third-level For drop-down menus, the source of E3 should be written as [=INDIRECT($D3)], so that three-level drop-down menu linkage can be achieved. If the source is written as [=INDIRECT($C$3)], multi-line and multi-level menu linkage cannot be achieved when dragging down in Excel.

The above is the detailed content of Detailed steps for creating a three-level linkage drop-down menu in Excel. For more information, please follow other related articles on the PHP Chinese website!