这问题我之前踩过坑！Excel的数据验证对跨表范围、动态命名范围的支持确实有局限性，直接用多表并集或者普通命名范围都会出问题，给你几个实用的解决办法：

方法1：用动态数组公式生成合并去重列表（适合Excel 365/2019+） #

这个方法不用VBA，纯公式就能搞定动态更新的下拉列表：

• 先在最终工作表的某个空白列（比如Z列，建议隐藏）的第一个单元格（Z1）输入公式：

  =UNIQUE(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,Sheet1!A:A,Sheet2!A:A,Sheet3!A:A)&"</s></t>","//s"))
  

  把Sheet1!A:A、Sheet2!A:A替换成你实际的各工作表唯一键列范围，公式会自动合并所有非空值并去重。
• 给这个动态范围创建动态命名范围：
  1. 打开「公式」选项卡 → 「定义名称」
  2. 名称填AllUniqueKeys，引用位置输入：

     =OFFSET(最终工作表!$Z$1,0,0,COUNTA(最终工作表!$Z:$Z),1)
     

     这里的「最终工作表」改成你的目标表名，这个公式会自动适配列表的长度变化。
• 最后设置数据验证：
  选中需要下拉列表的单元格，打开「数据」选项卡 → 「数据验证」，允许选「序列」，来源输入=AllUniqueKeys，确定后就能正常用了。

方法2：用VBA生成静态合并列表（适合所有Excel版本） #

如果你的Excel版本不支持动态数组，用VBA生成静态列表更稳妥：

• 按Alt+F11打开VBA编辑器，右键点击工作簿 → 「插入」→ 「模块」，粘贴以下代码：

  Sub CombineUniqueKeys()
      Dim ws As Worksheet, targetWs As Worksheet
      Dim keyCol As Range, combinedKeys As Collection
      Dim cell As Range, i As Integer
      
      '初始化集合用来存唯一键（自动去重）
      Set combinedKeys = New Collection
      '指定最终工作表，改成你的表名
      Set targetWs = ThisWorkbook.Worksheets("最终工作表")
      
      '遍历所有工作表，跳过最终表
      For Each ws In ThisWorkbook.Worksheets
          If ws.Name <> targetWs.Name Then
              '指定当前工作表的唯一键列，改成你的列范围
              Set keyCol = ws.Range("A:A")
              For Each cell In keyCol
                  '只取非空值，加入集合自动去重
                  If cell.Value <> "" Then
                      On Error Resume Next
                      combinedKeys.Add cell.Value, Key:=CStr(cell.Value)
                      On Error GoTo 0
                  End If
              Next cell
          End If
      Next ws
      
      '清空目标列（这里用Z列）并写入合并后的键
      targetWs.Range("Z:Z").ClearContents
      For i = 1 To combinedKeys.Count
          targetWs.Cells(i, "Z").Value = combinedKeys(i)
      Next i
  End Sub
  

• 修改代码里的工作表名和键列范围，按F5运行宏，最终工作表的Z列就会出现所有合并去重的唯一键。
• 给Z列的有效数据范围创建命名范围，再用数据验证引用这个范围即可。

为啥直接用命名范围不行？ #

Excel的数据验证「序列」来源有两个限制：

• 不支持直接引用跨多个工作表的范围，即使你把它做成命名范围也不行；
• 对动态数组公式的命名范围兼容性差，普通公式能识别，但数据验证读不到动态数组的结果。
  所以必须先把多表的键合并成单个工作表的连续范围，再用这个范围做数据验证的来源。

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