Excel中的下拉列表是强大的工具。They let you provide users with a drop-down arrow that, when selected, provides them with a list of choices.
这可以减少数据输入错误，因为它避免了用户必须直接输入答案。Excel甚至允许您从一系列单元格中提取这些下拉列表的项目。
然而，它并不止于此。使用一些创造性的方法为下拉单元格配置数据验证，您甚至可以创建多个链接的下拉列表，其中第二个列表中可用(available in a second list)的项目取决于用户在第一个列表中创建的部分。
多个链接(Linked)的下拉列表有什么(Good)用？
考虑到大多数在线表格会根据您在之前的下拉列表中回答的内容填写辅助下拉列表。这意味着您可以使Excel数据输入表与在线表格一样先进。它会根据用户的回答自行修改。
例如，假设您使用Excel电子表格从需要维修计算机(computer repairs)的用户那里收集计算机信息。
输入选项可能如下所示：
- 电脑部分(Computer Part)：显示器、鼠标(Mouse)、键盘(Keyboard)、基础系统(Base System)
- 零件类型：
- 显示器(Monitor)：玻璃、外壳(Housing)、电源线(Power Cord)、内部电子设备(Internal Electronics)
- 鼠标(Mouse)：滚轮，LED灯(LED Light)，线(Cord)，按钮(Buttons)，外壳
- 键盘(Keyboard)：按键、外壳(Housing)、薄膜(Membrane)、电源线(Cord)、内部电子元件(Internal Electronics)
- 基本系统(Base System)：外壳、按钮(Buttons)、端口(Ports)、电源(Power)、内部电子设备(Internal Electronics)、操作系统(Operating System)
正如您从该树中看到的，应可用于选择“部件类型”的信息取决于用户在第一个下拉列表中选择的计算机部件。(Computer Part)
在此示例中，您的电子表格可能开始看起来像这样：
如果您创建多个链接的下拉列表，您可以使用从 B1 中的下拉列表中选择的项目来驱动 B2 中下拉列表的内容。
让我们来看看如何设置它。此外，请随时下载我们的示例 Excel表，其中包含以下示例。
创建您的下拉列表源表(List Source Sheet)
设置此类内容的最简洁方法是在Excel中创建一个新选项卡，您可以在其中配置所有下拉列表项。
要设置这些链接的下拉列表，请创建一个表格，其中顶部的标题是您要包含在第一个下拉列表中的所有计算机部件。然后列出应该在该标题下的所有项目（零件类型）。
接下来，您需要选择并命名每个范围，以便稍后设置数据验证(Data Validation)时，您将能够选择正确的范围。
为此，请选择每列下的所有项目，并将所选范围的名称与标题相同。要命名表，您只需在“A”列上方的字段中键入名称。
例如，选择单元格A2到A5，并将该范围命名为“监视器”。
重复此过程，直到您拥有正确命名的所有范围。
另一种方法是使用 Excel 的“从选择中(Selection)创建(Create)”功能。这使您可以像上面的手动过程一样命名所有范围，但只需单击一下。
为此，只需选择您创建的第二张工作表中的所有范围。然后从菜单中选择公式，然后在功能区中选择(Formulas)从选择中创建(Create from Selection)。
将出现一个弹出窗口。确保只选择了Top row，然后选择OK。
这将使用顶行中的标题值来命名它下面的每个范围。
设置您的第一个下拉列表
现在是时候设置您的多个链接下拉列表了。去做这个：
1. 返回第一张纸，选择第一个标签右侧的空白单元格。然后从菜单中选择数据，然后在功能区中选择数据(Data)验证(Data Validation)。
2. 在打开的数据验证(Data Validation)窗口中，选择允许(Allow)下的列表(List)，然后在源(Source)下选择向上箭头图标。这将允许您选择要用作此下拉列表源的单元格范围。
3. 选择您设置下拉列表源数据的第二个工作表，然后仅选择标题字段。这些将用于填写您选择的单元格中的初始下拉列表。
4. 选择选择窗口中的向下箭头以展开数据验证(Data Validation)窗口。您将看到您选择的范围现在显示在Source字段中。选择确定(OK)完成。
5. 现在，回到主工作表，您会注意到第一个下拉列表包含第二个工作表中的每个标题字段。
现在您的第一个下拉列表已经完成，是时候创建您的下一个链接下拉列表了。
设置您的第一个下拉列表
根据在第一个单元格中选择的内容，选择要为其加载列表项的第二个单元格。
重复上述过程以打开“数据验证(Data Validation)”窗口。在允许(Allow)下拉列表中选择列表。(List)Source字段将根据在第一个下拉列表中选择的内容拉入列表项。
为此，请输入以下公式：
=INDIRECT($B$1)
INDIRECT函数是如何工作的？
此函数从文本字符串返回有效的Excel引用（在本例中为范围）。在这种情况下，文本字符串是第一个单元格 ($B$1) 传递的范围的名称。因此 INDIRECT 采用范围的名称，然后提供下拉数据验证以及与该名称关联的正确范围。
注意(Note)：如果您为第二个下拉菜单配置数据验证，而没有从第一个下拉菜单中选择值，您将看到一条错误消息。您可以选择是(Yes)忽略错误并继续。
现在，测试您的新的多个链接下拉列表。使用第一个下拉菜单选择一个计算机部件。当您选择第二个下拉列表时，您应该会看到该计算机部件的相应列表项。这些是您为该零件填写的第二张表上列中的零件类型。(on the second sheet)
在Excel(Excel)中使用多个链接(Multiple Linked)的下拉列表
如您所见，这是一种让您的电子表格更加动态的非常酷的方法。通过填写后续下拉列表以响应用户在其他单元格中选择的(select in other cells)内容，您可以使电子表格对用户的响应更加灵敏，数据也更加有用。
试试上面的技巧，看看你可以在电子表格中创建什么样的有趣的链接下拉列表。在下面的评论部分分享您自己的一些有趣的提示。(Share)
How to Create Multiple Linked Drop Down Lists in Excel
Drop-down lists in Excel are powerful tools. They let you provide users with a drop-down arrow that, when selected, provides them with a list of choices.
This can reduce data-entry errors because it avoids users from having to type answers directly. Excel even lets you pull the items for those drop-down lists from a range of cells.
However, it doesn’t stop there. Using some creative ways to configure data validation for drop-down cells, you can even create multiple, linked drop-down lists, where the items that are available in a second list depend on the section the user made in the first list.
What Are Multiple Linked Drop-Down Lists Good For?
Consider that most forms online fill out secondary drop-down lists based on what you answer in the drop-down list before it. This means you can make your Excel data-entry sheets just as advanced as online forms. It will modify itself based on the user’s answers.
For example, let’s say you’re using an Excel spreadsheet to collect computer information from your users who need computer repairs.
The entry options might look like this:
- Computer Part: Monitor, Mouse, Keyboard, Base System
- Part Type:
- Monitor: Glass, Housing, Power Cord, Internal Electronics
- Mouse: Wheel, LED Light, Cord, Buttons, Casing
- Keyboard: Keys, Housing, Membrane, Cord, Internal Electronics
- Base System: Casing, Buttons, Ports, Power, Internal Electronics, Operating System
As you can see from this tree, the information that should be available to select for “Part Type” depends on which Computer Part the user selects in the first dropdown list.
In this example, your spreadsheet may start out looking something like this:
If you create multiple, linked drop-down lists, you can use the item selected from the drop-down list in B1 to drive the contents of the drop-down list in B2.
Let’s take a look at how you can set this up. Also, feel free to download our example Excel sheet with the example below.
Create Your Drop-Down List Source Sheet
The cleanest way to set something like this up is to create a new tab in Excel where you can configure all of your drop-down list items.
To set up these linked drop-down lists, create a table where the header at the top are all of the computer parts you want to include in the first dropdown list. Then list all of the items (part types) that should go under that header.
Next, you’ll want to select and name each range so that when you’re setting up Data Validation later, you’ll be able to select the correct one.
To do this, select all of the items under each column and name that selected range the same as the header. To name a table, you simply type the name in the field over the “A” column.
For example, select cells A2 through A5, and name that range “Monitor”.
Repeat this process until you have all of the ranges named appropriately.
An alternative way to do this is using Excel’s Create from Selection feature. This lets you name all of the ranges like the manual process above, but with a single click.
To do this, just select all of the ranges in the second sheet that you created. Then select Formulas from the menu, and select Create from Selection in the ribbon.
A pop-up window will appear. Make sure only Top row is selected and then select OK.
This will use the header values in the top row to name each of the ranges underneath it.
Set Up Your First Drop Down List
Now it’s time to set up your multiple, linked drop-down lists. To do this:
1. Back on the first sheet, select the blank cell to the right of the first label. Then select Data from the menu, and select Data Validation in the ribbon.
2. In the Data Validation window that opens, select List under Allow, and under Source, select the up arrow icon. This will let you select the range of cells you want to use as the source for this drop-down list.
3. Select the second sheet where you set up the drop-down list source data, and then select only the header fields. These will be used to fill out the initial drop-down list in the cell you’ve selected.
4. Select the down arrow in the selection window to expand the Data Validation window. You’ll see the range you’ve selected now displayed in the Source field. Select OK to finish.
5. Now, back on the main sheet, you’ll notice that the first drop-down list contains each of the header fields from the second sheet.
Now that your first drop-down list is finished, it’s time to create your next, linked drop-down list.
Set Up Your First Drop Down List
Select the second cell that you want to load list items for depending on what’s selected in the first cell.
Repeat the process above to open the Data Validation window. Select List in the Allow drop-down. The Source field is what will pull in list items depending on what’s selected in the first drop-down list.
To do this, enter the following formula:
=INDIRECT($B$1)
How does the INDIRECT function work?
This function returns a valid Excel reference (in this case to a range), from a text string. In this case, the text string is the name of the range passed by the first cell ($B$1). So INDIRECT takes the name of the range and then provides the drop-down data validation with the correct range associated with that name.
Note: If you configure the data validation for this second drop-down without selecting a value from the first drop-down, you’ll see an error message. You can select Yes to ignore the error and continue.
Now, test your new multiple, linked drop-down lists. Use the first drop-down to select one of the computer parts. When you select the second drop-down, you should see the appropriate list items for that computer part. These were the part types in the column on the second sheet that you filled in for that part.
Using Multiple Linked Drop-Down Lists in Excel
As you can see, this is a very cool way to make your spreadsheets much more dynamic. By filling in subsequent drop-down lists in response to what users select in other cells, you can make your spreadsheets far more responsive to users, and the data far more useful.
Play around with the tips above and see what kind of interesting linked drop-down lists you can create in your spreadsheets. Share some of your own interesting tips in the comments section below.
