Excel中表数据中的依赖下拉列表的公式 [英] Formula for dependent Drop-down list from table data in Excel

问题描述

Excel Noob在这里.

我正在尝试基于单元格值创建一个下拉列表.下拉列表的数据在名为数据"的表中.我想用基于数据表中列(状态)的值填充一个下拉框.状态栏"有4个值:打开"，关闭"，开放未来"，保留".选择打开状态"后，我希望该下拉列表填充与此状态相关联的票证编号，但我无法实现.

该下拉菜单位于另一个选项卡中，该表中的信息以每张票证的页面浏览格式显示.否则，在显示信息时，我必须在显示票证详细信息时滚动行和列.

表结构如下:第1栏:票号专栏2:问题标题第3栏:说明第4列:适用于故障单的状态和其他列

我尝试在

单元格 C2 中的公式为

  = IF(B2 = Select_Status，ROW()-1，") 

其中 Select_Status 是我为您的主下拉列表所在的单元格指定的名称.在我的示例中，它是单元格 G2 .

逻辑是根据您选择的状态返回票号的行位置.

接下来，使用以下公式创建一个新的命名范围.在我的示例中，我将其命名为 FilteredList .

  = OFFSET($ A $ 1，MIN(FilteredTN),, COUNTIF(FilteredTN，> 0")) 

然后，您可以在辅助下拉列表中使用此命名范围:

这是您应该得到的:

如果您有任何疑问，请告诉我.欢呼声:)

Excel Noob here.

I am trying to create a dropdown list based on a cell value. The data for the dropdown list is in a table called "Data". I want to populate a drop-down box with values based on a column (Status) in the data table. Status Column has 4 values: Open, Closed, Open-Future, Hold. When the Open Status is selected, I would like the dropdown to populate the ticket numbers associated with that status, but I am unable to achieve it.

The drop-down is located in another tab, where the information in the table is presented in a page view format per ticket. Otherwise, when presenting the information, I have to scroll rows and columns when presenting the details of a ticket.

The table structure is as below; Column 1: Ticket No Column 2: Issue Title Column 3: Description Column 4: Status and other columns applicable to the ticket

I tried, conditional drop-down list examples described here but it did not work. My guess is due to the data structure in my table. Unfortunately, I am unable to change the column structure nor use VBA to address the issue and relying on a magic formula.

Hope I explained the issue with relevant details, please let me know if you need further information to derive to a solution. Note: I don't see an easy way to attach the sample document, as it's work computer and restrictions are put around using most of the file-sharing websites. So I have uploaded the screenshot of the spreadsheets refer to attached

Thanks Al Sheik

解决方案

Suppose you have sorted your Data table by Status, you can insert a helper column as shown below:

The formula in Cell C2 is

=IF(B2=Select_Status,ROW()-1,"")

where Select_Status is the name I gave to the cell where your primary drop down list sits. In my example it is cell G2.

The logic is to return the row position of the ticket number based on the status you selected.

Next, create a new named range using the following formula. In my example I have named it as FilteredList.

=OFFSET($A$1,MIN(FilteredTN),,COUNTIF(FilteredTN,">0"))

Then you can use this named range in your secondary drop down list:

Here is what you should get:

Let me know if you have any questions. Cheers :)

这篇关于Excel中表数据中的依赖下拉列表的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！