条件/级联/从属下拉列表 [英] Conditional/cascading/dependent drop-down list

问题描述

我本质上是在尝试执行此人正在尝试执行的操作:

住户编号是标识符.

我也去过这里

在第二张工作表上，我们将其称为 MasterList ，其中包含所有数据选项和相应的数据.第1行是标题行；第2行及以下是实际数据:

在第三张纸上，我们称之为 DropDownLists ，是魔术发生的地方.首先需要具有所有唯一数据选项的列表.我已将其放在A列中.您可以通过自己喜欢的任何方式(高级过滤器，数据透视表，公式，VBA等)从主列表中获得唯一的数据选项.该唯一数据选项列表是 Sheet1 单元格B1的下拉列表的基础.然后在 DropDownLists 的单元格B2中，并根据需要向下复制，以确保它将捕获与所选Data Option相关的所有数据，请使用以下公式(调整工作表名称和范围以适合您的实际数据):

  = IF(OR(Sheet1！$ B $ 1 ="，ROW(B1)> COUNTIF(MasterList！$ A $ 2:$ A $ 10000，Sheet1！$ B $ 1))，"，INDEX(MasterList！$ B $ 2:$ B $ 10000，MATCH(1，INDEX((MasterList！$ A $ 2:$ A $ 10000 = Sheet1！$ B $ 1)*(COUNTIF(B $ 1:B1，MasterList！$ B $ 2:$B $ 10000)= 0)，)，0))) 

这会使您的 DropDownLists 工作表看起来像这样:

最后，我们需要使该列表(基于所选Data Option的数据)成为动态的命名范围.我将其命名为 listFilteredData ，并使用以下公式对其进行了定义:

  = DropDownLists！$ B $ 2:INDEX(DropDownLists！$ B:$ B，MAX(2，ROWS(DropDownLists！$ B:$ B)-COUNTBLANK(DropDownLists！$ B:$ B))) 

然后为 Sheet1 单元格B2，使用数据验证并使用 = listFilteredData 定义列表，您将获得如我的示例所示的结果.

I am essentially trying to do that this guy is trying to do: Excel drop-down list using vLookup

I've gone through the steps and since my data set has about 400 different drop down options I am hoping there is an easier way than naming ranges. I have a list of about 400 different account names. Each of these account names is tied to a household and identified by the household ID number. A household can have anywhere from 1-5 account names. I would like for my drop down menu to be able to identify a household ID number and then provide the drop down with the account names associated with it.

Example:

Where household Id number is the identifier.

I've gone here as well http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/data-validation/dynamic-indirect and am trying to find a way to save on some of the manual work.I am a total newbie so thank you in advance for any help you can provide.

解决方案

Let's pretend you have three worksheets. The first worksheet, Sheet1, is the sheet that your users will be looking at and it has the drop down lists. We'll say that the cell containing the first drop down option is in B1 (the one with about 400 different options) and the cell containing the dependent drop down is in B2:

On your second sheet, which we'll call MasterList, is all of the data options and the corresponding data. Row 1 is a header row; Row 2 and down is the actual data:

On your third sheet, which we'll call DropDownLists, is where the magic happens. It first needs to have a list of all the unique data options. I've put that in column A. You can get the unique data options from your master list through whatever means you prefer (Advanced Filter, Pivot Table, formula, VBA, etc). That list of unique data options is the basis of your drop down list for Sheet1 cell B1. Then in cell B2 of DropDownLists and copied down as far as needed to guarantee it will grab all data associated with the selected Data Option, use this formula (adjust sheet names and ranges to suit your actual data):

=IF(OR(Sheet1!$B$1="",ROW(B1)>COUNTIF(MasterList!$A$2:$A$10000,Sheet1!$B$1)),"",INDEX(MasterList!$B$2:$B$10000,MATCH(1,INDEX((MasterList!$A$2:$A$10000=Sheet1!$B$1)*(COUNTIF(B$1:B1,MasterList!$B$2:$B$10000)=0),),0)))

This makes your DropDownLists sheet look like this:

Lastly, we need to make that list (data based on the selected Data Option) a dynamic named range. I named it listFilteredData and defined it with this formula:

=DropDownLists!$B$2:INDEX(DropDownLists!$B:$B,MAX(2,ROWS(DropDownLists!$B:$B)-COUNTBLANK(DropDownLists!$B:$B)))

Then for Sheet1 cell B2, use Data Validation and define the list with =listFilteredData and you will get results as shown in my example.

这篇关于条件/级联/从属下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！