如何基于多个命名范围构建Excel动态数据验证下拉列表

你遇到的报错确实是Excel数据验证的序列源对FILTER这类动态数组函数兼容性不足导致的，动态数组的溢出逻辑无法被数据验证的解析规则识别，因此会出现返回错误、下拉列表为空的问题。下面提供两种兼容传统函数的实现方案：

方案1：直接基于四个命名范围构造（最适配需求） #

这个方案完全依赖你已经定义的name1~name4四个命名范围，不受行内其他数据影响，就算后续命名范围指向的单元格位置变更，只要命名范围定义正确就可以正常运行：

1. 打开「名称管理器」，删除之前创建的all命名范围，重新新建名为all的命名范围，引用位置填写如下公式：
   =CHOOSE(ROW($1:$4),name1,name2,name3,name4)
   这个公式会按顺序返回四个命名范围的值，生成数据验证可识别的垂直一维数组。
2. 选中需要设置数据验证的单元格，打开数据验证设置窗口，允许类型选择「序列」，来源输入=all，点击确定即可。

效果说明 #

• 任意修改name1~name4对应单元格的值，下拉列表内容会自动同步更新
• 就算name1~name4的单元格位置调整，只要命名范围的指向正确，下拉列表就可以正常取值
• 不需要扫描整行内容，第3行后续新增其他数据不会干扰下拉列表的选项

方案2：整行扫描去空（适配你原有思路） #

如果你需要保留扫描第3行所有非空值作为下拉选项的逻辑，用传统兼容函数替换FILTER即可，all命名范围的引用位置填写如下公式：
=IFERROR(INDEX($3:$3,SMALL(IF($3:$3<>"",COLUMN($3:$3),4^8),ROW($A$1:INDEX($A:$A,COUNTA($3:$3))))),"")
直接输入公式即可，不需要额外按数组快捷键。

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