问题描述
这里的 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 NoColumn 2: Issue TitleColumn 3: DescriptionColumn 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
ThanksAl 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.
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中表格数据中相关下拉列表的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！