根据Microsoft的文档，您不能使用外部引用进行数据验证。您可以在以下链接中找到文档...
https://learn.microsoft.com/en-us/office/troubleshoot/excel/external-references-data-validation-fails
作为一种解决方法，您可以使用ExecuteExcel4Macro方法从关闭的工作簿访问值。您可以在以下链接中找到文档...
https://learn.microsoft.com/en-us/office/vba/api/excel.application.executeexcel4macro
使用此方法，将访问这些值以构建一个字符串，然后将其用作验证的源。但是，请注意，有255个字符的限制。
此外，这意味着每次打开工作簿时都需要更新数据验证，以确保具有最新值。若要自动执行此操作，可以使用工作簿打开事件过程。

• • 解决方案**

[常规模块]
下面的宏应该放在一个常规的模块中，并将添加来自外部源的数据验证...

Option Explicit

Sub UpdateDataValidation()

    Dim sourcePath As String
    sourcePath = "C:\Users\Domenic\Desktop\" 'change the path accordingly

    Dim sourceFileName As String
    sourceFileName = "Sample.xlsm" 'change the file name accordingly

    Dim sourceSheetName As String
    sourceSheetName = "Sheet1" 'change the sheet name accordingly

    Dim sourceReference As String
    sourceReference = "D2:D10" 'change the reference accordingly

    Dim currentCell As Range
    Dim currentValue As String
    Dim validationList As String

    validationList = ""
    For Each currentCell In Worksheets(1).Range(sourceReference) 'any worksheet reference will do for our purposes here
        currentValue = ExecuteExcel4Macro("'" & sourcePath & "[" & sourceFileName & "]" & sourceSheetName & "'!" & currentCell.Address(, , xlR1C1))
        validationList = validationList & "," & currentValue
    Next currentCell

    validationList = Mid(validationList, 2)

    With ThisWorkbook.Sheets("Sheet1").Range("A10:A100").Validation 'change the sheet name and range accordingly
        .Delete
        .Add _
            Type:=xlValidateList, _
            AlertStyle:=xlValidAlertStop, _
            Formula1:=validationList
    End With

End Sub

[此工作簿模块]
下面的宏应该放在ThisWorkbook的代码模块中，并且在打开工作簿时自动更新数据验证的值…

Option Explicit

Private Sub Workbook_Open()
    UpdateDataValidation
End Sub

您可以使用Excel数据模型在没有宏的情况下完成此操作。首先将下拉选项放在源工作簿中，并命名该范围（使用公式->名称管理器）。关闭此工作簿。在主工作簿中，转到数据-->获取数据-->从文件-->从Excel工作簿。然后选择源工作簿（需要关闭）并单击导入。从列表中选择命名范围。单击“加载”。然后单击主工作簿中加载的数据，单击Data --> Queries and Connections --> Properties，然后单击查询名称旁边的查询属性图标（一个带有两个正方形的矩形）。将数据设置为打开文件时刷新，以及每5分钟刷新一次。然后，可以使用主工作簿中的此数据副本作为数据验证列表的源。