当源数据变更时自动更新已填充的Excel下拉列表值

默认情况下，Excel不会自动更新已填充到单元格的下拉列表值——因为这些值是静态文本，并非动态引用源数据。但可以通过以下两种方法实现自动同步：

方法1：用动态公式绑定源数据（无需VBA，推荐） #

1. 将命名区域改为动态格式 #

打开「公式」选项卡 → 「名称管理器」，找到你的命名区域（比如SourceList），将其引用位置替换为动态公式：

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

这个公式会自动统计Sheet1列A的非空单元格数量，让命名区域随源数据的增减自动扩展或收缩。

2. 替换已填充值为动态引用公式 #

把Sheet2中B1-B10的静态文本替换为以下公式（假设你的命名区域叫SourceList）：

• 适用于Excel 365/2021的简洁版：

  =XLOOKUP(B1, SourceList, SourceList, "")
  

• 兼容旧版Excel的通用版：

  =INDEX(SourceList, MATCH(B1, SourceList, 0))
  

输入公式后按回车，再下拉填充到B10即可。当Sheet1的源数据条目名称修改时，Sheet2的对应单元格会自动同步更新。

注意：第一次输入公式会触发循环引用提示，解决方法是：「文件」→「选项」→「公式」，勾选「启用迭代计算」，设置迭代次数为1次。

方法2：用VBA宏自动更新（适合不想用公式的场景） #

1. 编写更新宏 #

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

Sub UpdateDropdownValues()
    Dim srcRange As Range
    Dim targetRange As Range
    Dim cell As Range
    Dim srcCell As Range
    
    ' 绑定你的命名区域和目标下拉区域
    Set srcRange = ThisWorkbook.Names("SourceList").RefersToRange
    Set targetRange = ThisWorkbook.Sheets("Sheet2").Range("B1:B10")
    
    ' 遍历目标区域，匹配并更新值
    For Each cell In targetRange
        If cell.Value <> "" Then
            For Each srcCell In srcRange
                If srcCell.Value = cell.Value Then
                    cell.Value = srcCell.Value
                    Exit For
                End If
            Next srcCell
        End If
    Next cell
End Sub

2. 设置自动触发事件 #

在VBA编辑器左侧双击Sheet1，在代码窗口的下拉菜单中选择「Worksheet」，再选择「Change」事件，粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅当修改源数据区域时触发更新
    If Not Intersect(Target, ThisWorkbook.Names("SourceList").RefersToRange) Is Nothing Then
        Call UpdateDropdownValues
    End If
End Sub

此后，只要Sheet1的源数据发生修改，Sheet2中已填充的下拉值会自动同步更新。

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