我有一个表，我希望用户使用数据验证来填充列表中的值。验证列表的内容应该基于在另外两个表中输入的值，这两个表被定义为动态命名范围（例如list1和list2）。我需要验证列表（比如listAll）在list1或list2中的值被更改或添加时自动更新。
举例来说：
我可以很容易地实现这一点与宏触发的变化，在列表1或列表2，但在这个特定的情况下，使用VBA是不允许的。我可以定义一个命名范围，它只是引用：=list1, list2。这确实创建了列表的虚拟组合，但只能在count或sum等函数中使用。在数据验证中使用组合列表会导致错误消息：
列表源必须是带分隔符的列表，或者是对单行或列的引用。
从类似的问题here和here和here和here看来，以前没有解决这个问题（没有VBA），但我认为它可能是解决与较新的excel函数。
我已经找到了一个解决方案，我将在下面发布，但它很麻烦，我希望有人能想出一些更简单，更优雅的东西。
3条答案
按热度按时间enyaitl31#
使用下面的公式合并两个或多个列表到一个使用
FILTERXML()和TEXTJOIN()公式。字符串
然后使用
#操作符将该动态范围引用到数据验证列表中，如=D2#。请参见下面的屏幕截图。的数据
kh212irz2#
通常，我使用一个辅助范围来组合用户定义的列表。由于我的
Excel中没有FILTER函数，我使用了一些公式来获得组合列表：1.将两个maximum可能长度列表合并为一个（列
F）。1.确定用户输入值的序列号（列
E）。1.在可能的最大列表（列
H）中对所有行进行编号。1.检查存在哪些序列号（列
I）。1.创建组合列表（列
J）。1.最后，创建一个命名范围。
100d1x
的字符串
的
2ul0zpep3#
这是我找到的解决方案--分三步（我有没有说繁琐？）：
第一步：
我调用每一个列表，都在一列中（示例中为
D），一个远远低于另一个-以确保顶部有足够的地方“进化”。第二步：
我在原始列上使用filter函数，使两个列表出现在一个列中，彼此堆叠在一起：
=FILTER(D:D,D:D<>"")我尝试输入这个作为新名称的定义，但它不能用于数据验证列表（“evaluates to an error”）。因此-第三步：
编辑-以下由@Harun24hr建议的出色改进：
现在我可以直接在数据验证窗口中引用步骤2中的单元格，如下所示：
=$F&1#的值。x1c 0d1x的数据
或者，我可以用相同的引用创建一个新名称，可以在数据验证或其他用途中使用。新名称动态地基于list 1和list 2。