使用公式作为源创建筛选型数据验证下拉列表时出现报错

报错根因 #

• 语法错误：你提交的公式存在字符缺失，Sheet1$A$1:$A$7部分的工作表名和区域之间缺少必要的!符号，正确格式应为Sheet1!$A$1:$A$7，语法错误会直接导致公式校验失败触发报错。
• 逻辑不兼容：IF函数返回的结果是包含空值的数组，旧版Excel不支持直接将数组作为数据验证的序列来源，即使高版本Excel能识别该公式，生成的下拉列表也会出现大量空白选项，无法正常使用。

对应解决方法 #

适用Excel 365/2021及以上版本 #

直接替换原有公式为=FILTER(Sheet1!$A$1:$A$7,Sheet1!$C$1:$C$7="Yes")即可，FILTER会自动过滤掉不符合条件的内容，返回的纯有效内容数组可直接作为数据验证的序列来源，无空白选项。

适用Excel 2019及更低版本 #

先增设辅助列处理筛选逻辑：

1. 在Sheet1的空白列（例如D列）D1单元格输入公式=IFERROR(INDEX($A$1:$A$7,SMALL(IF($C$1:$C$7="Yes",ROW($A$1:$A$7),9^9),ROW(A1))),"")
2. 按下Ctrl+Shift+Enter组合键触发数组运算
3. 按住D1单元格右下角的填充柄下拉到D7单元格，此时D列会按顺序展示所有C列为Yes对应的A列内容，多余行显示为空
4. 数据验证的序列来源设置为=OFFSET(Sheet1!$D$1,0,0,COUNTA(Sheet1!$D$1:$D$7),1)，即可自动识别有效内容生成无空白的下拉列表。

内容的提问来源于stack exchange，提问作者Programmer