在数据验证中按公式引用命名范围

大家好,大家好。

我有一系列命名范围, A_Colours ,则, B_Colours 等 这些代表颜色列表中已排序列的部分,按生产这些颜色的制造商的代码排序。这些是动态命名范围,由 COUNTA 制造商代码有多少种颜色。根据我的计算,列表应该是连续的,因为列是按代码排序的。

我知道我可以通过简单的设置来参考数据验证中的范围 =A_Colours 作为列表源(无引号)。这 确实有效 在这种情况下。

但是,我有一个下拉列表来控制要在子下拉列表中显示哪个a-M范围。 例如,A1中的下拉列表将A、B、C、D显示为选项。 B1中的下拉列表显示相对于A1中所选选项的下拉列表选项。

示例: 我从A1中的列表中选择A。 在单元格B1中,我希望下拉列表显示命名范围中的所有值 A\U颜色 。

我尝试了各种方法将文本字符串A\U颜色传递到DV列表源字段,但都失败了。公式中有错误,或者范围计算为错误,或者它只允许我从下拉列表中选择A\U颜色。

示例1: A1选择A B1 DV箱:

=INDIRECT("$A$1")&"_Colours"

这会出现错误“列表源必须是带分隔符的列表,或对单行或列的引用。”

这可能是因为表列表已排序?我不这么认为,因为上面的第一个例子很有效。

示例2: 使用C1中的辅助单元格创建文本字符串“A\U颜色”,公式如下: =$A$1&“\u颜色”

然后在B1单元的DV框中:

=INDIRECT("$C$1")

请帮我做到这一点! 非常感谢。

[编辑]解决方案:

按照以下指示创建新图纸。 为每个制造商创建标题(单元格A1中的“A”)。 在A2中输入公式(Ctrl+Shift+Enter作为数组公式):

=索引(颜色,小号(IF)((索引(颜色,1)=索引(MFR,匹配(A$1,MFR[MFRName],0),2)),匹配(行(颜色),行(颜色)),“”),行(B$1:B1)),列(A$1:$B1))

我对它进行了一些修改,但这要归功于 https://www.get-digital-help.com/2009/09/28/extract-all-rows-from-a-range-that-meet-criteria-in-one-column-in-excel/

将公式应用到最后一个加上几个以允许扩展。 将公式应用于上一个标题。

现在,您的列表肯定是连续的、单列的,并且不是动态的,因此满足数据验证的严格标准。

为每个列表创建一个命名范围,使用确切的名称保持简单,但我选择在末尾添加“名称”,例如MFR1colors。

在DV源列表字段中引用它们(对于原始表中的单元格B2),如下所示: =间接(2美元和“颜色”)

其作用: 颜色表中的外观: 制造商代码颜色代码

从制造商表“MFR”中的原始表(“A”)单元格A1中查找MFR代码的第一个匹配实例,该表有以下列: MFRName代码

按火柴拉出一行(行(颜色,行(颜色),“”)。

按末尾的行和列检查所需的行和列。