Excel 2016技术求助：根据下拉列表选项动态更改单元格公式

方法一：使用工作表Change事件（推荐） #

当B2的下拉选项改变时，自动更新目标单元格的公式，步骤如下：

1. 右键目标工作表标签，选择「查看代码」打开工作表代码窗口
2. 粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅监听B2单元格的内容变化
    If Not Intersect(Target, Me.Range("B2")) Is Nothing Then
        Dim monthCode As String
        Dim filePath As String
        Dim targetRanges As Range
        
        ' 定义需要更新公式的单元格区域
        Set targetRanges = Me.Range("B8:E8,B9:E9")
        
        ' 映射月份名称到对应的文件夹编码
        Select Case UCase(Me.Range("B2").Value)
            Case "JAN": monthCode = "012023"
            Case "FEB": monthCode = "022023"
            Case "MAR": monthCode = "032023"
            Case "APR": monthCode = "042023"
            Case "MAY": monthCode = "052023"
            Case "JUNE": monthCode = "062023"
            Case "JULY": monthCode = "072023"
            Case "AUG": monthCode = "082023"
            Case "SEPT": monthCode = "092023"
            Case "OCT": monthCode = "102023"
            Case "NOV": monthCode = "112023"
            Case "DEC": monthCode = "122023"
            Case Else: Exit Sub ' 遇到无效选项直接退出
        End Select
        
        ' 拼接外部文件的完整引用路径
        filePath = "'X:\OT Management\" & monthCode & "\[9021 SMelt.xlsx]Summary'!F$6"
        
        ' 批量设置目标区域的公式，请替换[你的其他公式部分]为实际计算内容
        targetRanges.Formula = "=" & filePath & "+[你的其他公式部分]"
    End If
End Sub

代码说明： #

• Worksheet_Change事件会在工作表单元格内容变更时触发，通过Intersect精准判断是否是B2的变化
• Select Case完成月份名称到文件夹编码的映射，确保路径匹配正确
• 批量更新目标区域公式，避免逐个单元格操作的繁琐

方法二：使用INDIRECT函数（仅适用于外部文件打开状态） #

如果不想用VBA，可尝试公式方案，但外部文件必须处于打开状态才会生效：

在B8单元格输入以下公式，再向右、向下填充至E9：

=INDIRECT("'X:\OT Management\"&VLOOKUP(B2,{"JAN","012023";"FEB","022023";"MAR","032023";"APR","042023";"MAY","052023";"JUNE","062023";"JULY","072023";"AUG","082023";"SEPT","092023";"OCT","102023";"NOV","112023";"DEC","122023"},2,FALSE)&"\[9021 SMelt.xlsx]Summary'!F$6")+[你的其他公式部分]

注意事项： #

• 外部文件未打开时，公式会返回#REF!错误
• 公式较长，后续维护成本高于VBA方案

补充：优化下拉列表代码 #

原代码可增加删除已有验证的逻辑，避免重复添加时报错：

Sub CreateDropDownList()
    With Range("B2").Validation
        .Delete ' 先清除已有验证规则
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="JAN,FEB,MAR,APR,MAY,JUNE,JULY,AUG,SEPT,OCT,NOV,DEC"
        .IgnoreBlank = True
        .InCellDropdown = True
    End With
End Sub

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