Excel如何基于单元格字符串动态填充下拉列表且无需额外辅助单元格

步骤1：调整辅助公式 #

你可以把原方案里每个单元格对应的搜索字符串辅助格，改成仅使用1个全局辅助单元格，存到你现有辅助工作表的任意空白位置（比如辅助表的A1单元格），再把你原来的B列动态数组公式修改为：
=SORT(FILTER(Sheet1!A2:A7,ISNUMBER(SEARCH(辅助表!$A$1,Sheet1!A2:A7)),""))
该公式会固定引用这一个全局辅助单元格的内容作为搜索关键词，不需要每个D列单元格对应单独的辅助存储格。

步骤2：添加VBA事件代码 #

按Alt+F11打开VBA编辑器，双击D列所在的工作表，把以下代码粘贴到代码窗口中：

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 仅对D列第2行及以下的单元格生效
    If Target.Column = 4 And Target.Row >= 2 Then
        ' 将当前选中的D列单元格值同步到全局搜索辅助单元格
        Sheets("辅助表").Range("A1").Value = Target.Value
        ' 刷新当前单元格的数据验证列表
        Target.Validation.Modify Formula1:="=" & Sheets("Sheet1").Range("B2").Address & "#"
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 And Target.Row >= 2 Then
        ' 输入内容变化时实时更新搜索关键词，刷新下拉选项
        Sheets("辅助表").Range("A1").Value = Target.Value
        On Error Resume Next
        ' 校验输入内容是否属于匹配列表
        Dim matchFlag As Variant
        matchFlag = Application.Match(Target.Value, Sheets("Sheet1").Range("B2#"), 0)
        If IsError(matchFlag) Then
            MsgBox "输入内容不符合可选范围，请从下拉列表选择或输入匹配的关键词", vbExclamation
            Target.Activate
        End If
    End If
End Sub

注意将代码中的「辅助表」替换为你实际的辅助工作表名称，Sheet1替换为存储选项列表的工作表名称

步骤3：设置D列统一数据验证 #

选中D列从第2行开始的所有需要应用功能的单元格，设置数据验证类型为「序列」，来源填写：
=Sheet1!$B$2#
勾选「提供下拉箭头」，系统自带的出错警告可以关闭，使用VBA的自定义提示即可。

实现效果 #

• 点击D列任意单元格输入字符时，输入内容会实时同步到全局辅助单元格，B列的FILTER公式会自动筛选匹配选项，下拉列表仅展示和输入内容匹配的项
• 输入完成离开单元格时会自动校验内容合法性，不符合匹配规则会弹出提示
• 整列D复用同一套逻辑，不需要单独为每个单元格配置辅助存储，仅占用1个辅助工作表的空白单元格，符合你的使用要求

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