创建具有过滤范围的单元内下拉列表 [英] Create in-cell dropdown with filtered range

问题描述

我正在使用Google Spreadsheet.

I'm using Google Spreadsheet.

为了说明我的问题，我将范围A2:A8用于D2和E2的数据验证.

To illustrate my problem, I use the range A2:A8 for the data validation of D2 and E2.

但是因为在单元格D2中，您只应选择一种动物，所以我想用B2:B8过滤范围.

But because in cell D2, you are supposed to select an animal only, I'd like to filter the range with B2:B8.

我尝试使用的是自己的公式:

What I've tried, is using my own formula which is :

=FILTER(A2:A8;IS("B2:B8";"ANIMAL"))

但是这不起作用，如果我使用自定义公式，则无法选择下拉菜单"选项.

but this won't work and I cannot pick the "dropdown" option if I use custom formula.

我也在范围"选择中尝试了公式，但这是无效的.包含过滤数据的下拉列表的正确公式是什么?

I've also tried my formula in my Range selection, but it's not valid. What is the right formula to use to have a dropdown with filtered data?

有什么想法吗?

推荐答案

就目前而言，在Google表格中，本地(即不借助Google Apps脚本)的唯一方法是填充下拉列表-down列表用于使用逗号分隔的列表，或引用一个范围.因此，根据您的情况，您需要在电子表格中的某处中复制您的过滤列表(可能在隐藏的工作表上):

As it stands, in Google Sheets, the only way to natively (that is, without resorting to Google Apps Script) populate drop-down lists is to use a comma-separated list, or reference a range. So in your case you would need to reproduce your filtered list somewhere in the spreadsheet (could be on a hidden sheet):

=FILTER(A2:A8;B2:B8="ANIMAL")

，然后在数据验证中引用该输出的范围.

and then reference the range of that output in Data validation.

使用公式直接生成下拉列表的功能将是一项功能强大的功能，并且已被许多人作为功能请求提交(您可能也想这样做:帮助菜单，报告问题).

The ability to use a formula to generate the drop-down list directly would be a powerful feature, and has been submitted as a feature request by many (you might like to do the same: Help menu, Report an issue).

这篇关于创建具有过滤范围的单元内下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！