如何创建用于数据验证的动态列表源？

Question

目前，我正在使用下面的内容来检索数据验证的值，问题是当我将其设置为Source时，我得到了The list source must be a delimited list, or a reference to a single row or column. --如何使它作为数据验证源使用？

=TRIM(LEFT(SUBSTITUTE(MID(INDEX($B:$B,MATCH($A3,$A:$A,0)),FIND("|",SUBSTITUTE(INDEX($B:$B,MATCH($A3,$A:$A,0)),",","|",1))+1, LEN(INDEX($B:$B,MATCH($A3,$A:$A,0)))),",",REPT(" ",LEN(INDEX($B:$B,MATCH($A3,$A:$A,0))))),LEN(INDEX($B:$B,MATCH($A3,$A:$A,0)))))

我想做的是做一个索引/匹配，然后从列中找到值，然后检索第n个值。

例如：

1. 在A列中查找MyValueA
2. 返回C列(MyValueC1, MyValueC2, MyValueC3)中的值。
3. 数据验证作为下拉列表使用，允许用户选择第一项或不选择任何内容。
4. 列M-用户可以使用下拉列表从步骤2中选择第一项--在本例中是MyValueC1
5. 类似于列N到R。

我不能用的东西：

• UDF不适用于数据验证
• 定义的名称不能工作，因为它将值设置为静态的。

​

​

颜色为绿色的单元格应该具有数据验证下拉列表，供用户选择场景。

• 第1行用作标题。
• 第2行是场景#1的摘要描述符，它有三个场景(场景1、场景2、场景3)。
• 用户使用第3行和第4行来选择他们希望具有特定颜色的场景，但是使用数据验证下拉列表的选择列(从M列到R列)由场景可用的场景决定。
  • 选择1链接到第3行和第4行场景1的场景1(下拉仅显示场景1)
  • 选择2链接到第3行和第4行场景1的场景2(下拉仅显示场景2)
  • 选择3链接到第3行和第4行场景1的场景#3 (下拉仅显示场景#3)
  • 对于第3行和第4行，选择#4链接到场景#1的第4场景(但没有第4场景，因此下拉显示应为空白)
  • 选择#5链接到第3行和第4行场景1的场景#5 (但没有场景#5，因此下拉显示应为空)
  • 对于第3行和第4行，选择#6链接到场景#1的场景#6 (但没有场景#6，因此下拉显示应为空)

​

然后，对于场景2(第5行和第6行)和场景#3 (第7和第8行)，也有相同的想法。

在单元格中工作的公式，但不适用于数据验证源：

从B列中单元格的场景列表中选择第一个场景：=LEFT(SUBSTITUTE(INDEX(B:B,MATCH(A3,A:A,0)), ",", "|", 1),FIND("|",SUBSTITUTE(INDEX(B:B,MATCH(A3,A:A,0)), ",", "|", 1),1)-1)

从B列中单元格的场景列表中选择第二个场景：=TRIM(LEFT(SUBSTITUTE(MID(INDEX($B:$B,MATCH($A3,$A:$A,0)),FIND("|",SUBSTITUTE(INDEX($B:$B,MATCH($A3,$A:$A,0)),",","|",1))+1, LEN(INDEX($B:$B,MATCH($A3,$A:$A,0)))),",",REPT(" ",LEN(INDEX($B:$B,MATCH($A3,$A:$A,0))))),LEN(INDEX($B:$B,MATCH($A3,$A:$A,0)))))

Answer 1

如果我的理解是正确的，你有：

1. 一个场景列表，至少1到3个场景，以及每个场景的场景列表。
2. 适用于每个场景的场景颜色列表(根据所提供的样本提供4种选择)。
3. 选择，从1到6，对应于每个场景\场景(即选择#n总是对应于场景#X \场景#n)，因此每个场景\场景\场景颜色只有一个可能的选择。
4. 为每个场景\场景分配场景颜色的表(一个场景\场景可能有几种场景颜色，即场景#1\场景#1)

这一解决办法包括：

1. 在工作表级别(范围)添加一个已定义的名称(名为DATA__)，将所有场景作为标题包含在每个场景下面的所有相应场景中--每个单元格[A1:C4]一个

​

图1

1. 添加一个列表来保存所有的场景库[E1:E5] -如果您也希望将场景颜色作为下拉列表，则可以选择

​

图2

1. 添加一个动态定义的名称(名为DtaVld__)，以允许使用下拉列表来分配场景颜色：

选择单元格I2并使用此公式添加动态定义的名称：

=INDEX(DATA,1+RIGHT(I$1,1),MATCH($G2,INDEX(DATA,1,0),0))

​

图3

1. 添加一个范围(结果表)来分配场景颜色[G1:N5]

由于“结果表”基本上将场景颜色分配给场景\场景，我冒昧地将场景颜色字段移到表的开头。

​

见图4

按字段在“结果表”中输入下列公式\DataValidations：

方案:允许选择方案的使用此公式添加数据验证以指向定义名称DATA的标题

=INDEX(DATA,1,0)

​

见图5。

场景颜色:允许选择场景颜色的添加指向$E$2:$E$5中列表的数据验证

Selection#n‘s (all cell I2:N5__)：添加指向动态定义名称DtaVld的数据验证