我来给你分享几个能解决这个跨工作簿依赖下拉问题的实用方法，亲测有效：

方法1：名称管理器+OFFSET函数（兼容旧版Excel） #

这个方法适配所有Excel版本，但需要保持Workbook2处于打开状态：

• 先在Workbook2里规整好数据：把Services对应的所有选项放在同一列，Medical Devices的选项放在另一列，且第一行是分类标题（比如A1=Services，B1=Medical Devices）。建议给每个分类的选项区域定义名称，比如Services_Options指向Workbook2!Sheet1$A$2:$A$5，Medical_Devices_Options指向Workbook2!Sheet1$B$2:$B$4，用下划线代替空格避免出错。
• 回到Workbook1，打开公式→名称管理器，新建一个名为Dependent_Options的名称，在「引用位置」输入：

  =OFFSET([Workbook2.xlsx]Sheet1!$A$1,MATCH(Workbook1!$C$2,[Workbook2.xlsx]Sheet1!$A$1:$B$1,0),1,COUNTA(OFFSET([Workbook2.xlsx]Sheet1!$A$1,0,MATCH(Workbook1!$C$2,[Workbook2.xlsx]Sheet1!$A$1:$B$1,0),1000,1)),1)
  

  逻辑是先用MATCH定位C2选中分类在Workbook2标题行的位置，再用OFFSET动态提取对应列的所有非空选项。
• 最后给Workbook1的D2设置数据验证：选择「序列」，来源输入=Dependent_Options即可。

方法2：XLOOKUP+动态数组（适合Excel 365/2021及以上） #

如果你的Excel是新版支持动态数组的，这个方法更简洁：

• 先在Workbook2把数据做成结构化表格（插入→表格），格式参考：

  Category	Options
  Services	Third party service
  Services	On-site support
  Medical Devices	Dosimetry
  Medical Devices	Imaging equipment

• 在Workbook1找个空白单元格（比如E2，嫌碍眼可以隐藏这列），输入公式：

  =XLOOKUP($C$2,[Workbook2.xlsx]Sheet1[Category],[Workbook2.xlsx]Sheet1[Options],"",0,1)
  

  这个公式会自动返回C2选中分类对应的所有选项，形成动态数组。
• 给D2设置数据验证，来源直接输入=$E$2#（#是动态数组的溢出运算符，会自动包含所有返回的选项）。同样需要Workbook2保持打开。

方法3：VBA实现（无需保持外部工作簿打开） #

如果想在Workbook2关闭的情况下也能用，VBA是最佳选择：

• 打开Workbook1，按Alt+F11打开VBA编辑器，在左侧找到对应的工作表（比如Sheet1）双击打开，粘贴以下代码：

  Private Sub Worksheet_Change(ByVal Target As Range)
      ' 只监听C2单元格的变化
      If Target.Address = "$C$2" And Target.Value <> "" Then
          Dim wb As Workbook
          Dim ws As Worksheet
          Dim lastRow As Long
          Dim optionsList As Variant
          
          ' 先检查Workbook2是否已打开
          On Error Resume Next
          Set wb = Workbooks("Workbook2.xlsx")
          On Error GoTo 0
          
          ' 如果没打开，就后台打开它
          If wb Is Nothing Then
              ' 替换成你Workbook2的实际保存路径
              Set wb = Workbooks.Open("C:\YourFolderPath\Workbook2.xlsx")
              wb.Windows(1).Visible = False ' 不显示窗口，后台运行
          End If
          
          Set ws = wb.Sheets("Sheet1")
          ' 找到选中分类所在的列
          Dim categoryCol As Integer
          categoryCol = ws.Rows(1).Find(What:=Target.Value, LookIn:=xlValues, LookAt:=xlWhole).Column
          
          ' 获取该列所有非空选项
          lastRow = ws.Cells(ws.Rows.Count, categoryCol).End(xlUp).Row
          optionsList = ws.Range(ws.Cells(2, categoryCol), ws.Cells(lastRow, categoryCol)).Value
          
          ' 更新D2的数据验证
          Me.Range("D2").Validation.Delete
          With Me.Range("D2").Validation
              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(Application.Transpose(optionsList), ",")
              .IgnoreBlank = True
              .InCellDropdown = True
          End With
          
          ' 如果是后台打开的Workbook2，用完就关掉
          If wb.Windows(1).Visible = False Then
              wb.Close SaveChanges:=False
          End If
          
          ' 释放对象
          Set wb = Nothing
          Set ws = Nothing
      End If
  End Sub
  

• 把代码里的路径改成你Workbook2的实际路径，然后保存Workbook1为启用宏的工作簿（.xlsm格式）。以后只要C2选了值，D2的下拉选项就会自动从Workbook2加载，哪怕Workbook2是关闭的。

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