excel下拉列表数据筛选_从筛选的Excel列表中下拉

excel下拉列表数据筛选

Someone asked me how to make a data validation drop down that only shows the visible rows from a filtered list. I created a sample file that shows how you can do that, and here are the details on setting up a drop down from filtered Excel list.

有人问我如何使数据验证下拉列表仅显示过滤列表中的可见行。 我创建了一个示例文件来显示如何执行此操作，这是从过滤的Excel列表中设置下拉菜单的详细信息。

视频：从过滤列表中下拉 (Video: Drop Down from Filtered List)

Update: Here's a new video that shows how to set up the drop down from a filtered list, step by step. The written instructions are below the video.

更新 ：这是一个新视频，逐步显示如何从过滤列表中设置下拉菜单。 书面说明在视频下方。

演示地址

员工名单 (Employee List)

In this example, the workbook has a list of employees, in a formatted Excel table (tblEmp).

在此示例中，工作簿在格式化的Excel表 (tblEmp)中具有一个雇员列表。

There is a named range – EmpList – based on the Employees column in that table.

基于该表中的“雇员”列，有一个命名范围 – EmpList。

简单的下拉列表 (Simple Drop Down List)

If you create a data validation drop down list, it shows all the items from the source list. There are basic instructions for setting up a drop down list on my Contextures website.

如果创建数据验证下拉列表，它将显示源列表中的所有项目。 在我的Contextures网站上有设置下拉列表的基本说明 。

In the sample workbook, there is a schedule sheet, with a drop down list based on the EmpList named range.

在示例工作簿中，有一个计划表，其中有一个基于EmpList命名范围的下拉列表。

Click the arrow in the Staff column, and select any of the employee names.

单击“员工”列中的箭头，然后选择任何员工名称。

过滤名称列表 (<