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

问题描述

这里的 Excel 菜鸟.

我正在尝试根据单元格值创建下拉列表.下拉列表的数据位于名为数据"的表中.我想用基于数据表中的列(状态)的值填充下拉框.状态栏有 4 个值:Open、Closed、Open-Future、Hold.选择打开状态后，我希望下拉列表填充与该状态关联的票号，但我无法实现.

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

表结构如下；第 1 列:票号第 2 列:问题标题第 3 列:描述第 4 栏:状态和适用于该票证的其他栏

我尝试过，条件下拉列表示例描述了

Cell 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屋！