嗨，我来帮你解决这个问题！你遇到的情况确实挺常见的——想用数据验证做一个随筛选操作和类别选择动态更新的唯一值列表，OFFSET函数确实搞不定筛选后的可见行识别和去重需求，我给你两种实用的解决方案，适配不同版本的Excel：

方案一：用Excel 365/2021动态数组函数（无需宏） #

如果你的Excel版本支持动态数组（365或2021及以后），这个方案最便捷，而且不需要写代码：

1. 先把你的数据转换成结构化表格
   选中数据区域，按Ctrl+T创建结构化表（命名为Table1，这样新增行时会自动纳入范围）。

2. 创建Category的下拉选择器
   在目标工作表（比如Sheet2）的A1单元格设置数据验证：

   • 类型选「序列」
   • 来源输入：=UNIQUE(Table1[Category])
     这样A1就能选择唯一的类别值。

3. 生成动态的唯一可见Product列表
   在Sheet2的C1单元格输入以下公式：

   =UNIQUE(FILTER(Table1[Product], (Table1[Category]=Sheet2!$A$1)*(SUBTOTAL(103,OFFSET(Table1[Category],ROW(Table1[Category])-ROW(Table1[#Headers]),0,1))=1)))
   

   公式解释：

   • SUBTOTAL(103,...)：识别筛选后可见的行（103代表忽略隐藏行的计数，返回1表示该行可见）
   • FILTER：筛选出符合选中Category且可见的Product
   • UNIQUE：对筛选结果去重，得到唯一值列表

4. 把动态列表绑定到数据验证
   在需要Product下拉的单元格（比如Sheet2的B1）设置数据验证：

   • 类型选「序列」
   • 来源输入：=Sheet2!$C$1#
     这里的#表示引用C1单元格溢出的整个动态数组区域，当表格筛选或Category选择变化时，列表会自动更新。

方案二：VBA宏方案（适配旧版Excel） #

如果你的Excel版本不支持动态数组，用VBA可以实现同样的效果，步骤如下：

1. 打开VBA编辑器
   按Alt+F11打开编辑器，插入一个模块（右键工作簿→插入→模块），粘贴以下代码：

   Sub UpdateDynamicProductList()
       Dim wsSource As Worksheet
       Dim wsTarget As Worksheet
       Dim tbl As ListObject
       Dim categoryFilter As String
       Dim rngVisible As Range
       Dim cell As Range
       Dim uniqueProducts As Collection
       Dim outputRng As Range
       Dim i As Integer
       
       ' 自定义设置：修改成你的工作表和表格名称
       Set wsSource = ThisWorkbook.Sheets("Sheet1")
       Set wsTarget = ThisWorkbook.Sheets("Sheet2")
       Set tbl = wsSource.ListObjects("Table1") ' 你的结构化表名称
       categoryFilter = wsTarget.Range("A1").Value ' Category选择单元格
       Set outputRng = wsTarget.Range("C1") ' 唯一Product列表的起始单元格
       
       ' 清空旧列表
       On Error Resume Next
       outputRng.Resize(wsTarget.Cells(wsTarget.Rows.Count, outputRng.Column).End(xlUp).Row - outputRng.Row + 1).ClearContents
       On Error GoTo 0
       
       ' 初始化集合存储唯一值
       Set uniqueProducts = New Collection
       
       On Error Resume Next ' 忽略重复值添加错误
       ' 遍历可见的Product行，筛选符合Category的唯一值
       For Each cell In tbl.ListColumns("Product").DataBodyRange.SpecialCells(xlCellTypeVisible)
           If tbl.ListColumns("Category").DataBodyRange.Cells(cell.Row - tbl.HeaderRowRange.Row, 1).Value = categoryFilter Then
               uniqueProducts.Add cell.Value, Key:=CStr(cell.Value)
           End If
       Next cell
       On Error GoTo 0
       
       ' 将唯一值写入目标区域
       For i = 1 To uniqueProducts.Count
           outputRng.Cells(i, 1).Value = uniqueProducts(i)
       Next i
       
       ' 更新数据验证（假设Product下拉在Sheet2的B1）
       With wsTarget.Range("B1").Validation
           .Delete
           .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
                Formula1:="=" & outputRng.Address & ":" & outputRng.Cells(uniqueProducts.Count, 1).Address
           .IgnoreBlank = True
           .InCellDropdown = True
       End With
   End Sub
   

2. 添加触发事件
   双击目标工作表（Sheet2）的模块，粘贴以下代码，实现自动更新：

   ' 表格筛选变化时触发更新
   Private Sub Worksheet_Calculate()
       UpdateDynamicProductList
   End Sub
   
   ' Category选择单元格变化时触发更新
   Private Sub Worksheet_Change(ByVal Target As Range)
       If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
           UpdateDynamicProductList
       End If
   End Sub
   

3. 启用宏
   保存工作簿为「启用宏的工作簿（.xlsm）」，打开时启用宏，之后你筛选Sheet1的表格或修改Sheet2的Category选择时，Product下拉列表会自动更新为符合条件的唯一可见值。

备注：内容来源于stack exchange，提问作者arfan khan