Excel-筛选表中的数据验证列表 [英] Excel - Data Validation list from filtered table

问题描述

我正在寻找一种方法来使数据验证列表从已过滤的表中提取.前任.我有一个名为客户的工作表，其中有一个名为CustomerList的表列A =客户B =地址C =城市D =州在另一个名为Quote的工作表上，我具有客户名称的单元格C13，该单元格具有数据验证列表到工作表客户"列A客户的动态范围.在我的列表中，即使我过滤表格以仅显示处于特定状态的客户，它也会显示所有1800个客户.我希望能够在表上设置过滤器以对客户进行排序，并让我的数据验证列表仅显示过滤列表中显示的客户.对于我的一生，我无法弄清楚.任何帮助将不胜感激.TIA.

I am looking for a way to make a data validation list pull from a filtered table. Ex. I have a sheet called customers with a table named CustomerList column A=Customer B=Address C=City D=State On another sheet named Quote I have cell C13 for customer name which has a data validation list to a dynamic range of Sheet Customers column A Customer. In my list it shows all 1800 customers even if i filter the table to show only customers in a certain state. I would like to be able to set filters on the table to sort my customers and have my data validation list only show the customers shown on the filtered list. For the life of me I can not figure this out. Any help would be greatly appreciated. TIA.

推荐答案

在工作表 Customers 中，选择一些单元格并输入:

In sheet Customers, pick some cell and enter:

=SUBTOTAL(103,A:A)

每次更改 A 列的过滤器时，都会重新计算此公式.

This formula will be re-calculated every time the filter is changed for column A.

在客户工作表代码区域中，安装以下事件宏:

In the Customers worksheet code area, install the following event macro:

Private Sub Worksheet_Calculate()
    Call makeDV
End Sub

在标准模块中，安装以下代码:

In a standard module, install the following code:

Public DVList As String

Public Sub makeDV()

    Dim A As Range, r As Range
    Dim c As Collection, v As Variant
    Set c = New Collection
    Set A = Intersect(Range("A2:A" & Rows.Count), ActiveSheet.UsedRange).Cells.SpecialCells(xlCellTypeVisible)
    DVList = ""

    On Error Resume Next

    For Each r In A
        v = r.Value
        If v <> "" Then
           c.Add v, CStr(v)
           If Err.Number = 0 Then
               DVList = DVList & "," & v
           Else
            Err.Clear
           End If
        End If
    Next r

    If Len(DVList) <> 0 Then DVList = Mid(DVList, 2)
    On Error GoTo 0

    Dim Quote As Worksheet
    Set Quote = Sheets("Quote")

    With Quote.Range("C13").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=DVList
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

只要在表 Customers 上修改了过滤器，表 Quotes 上单元格 C13 的数据验证就会更新.

Whenever the filter is modified on sheet Customers, the data validation for cell C13 on sheet Quotes will up updated.

这篇关于Excel-筛选表中的数据验证列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！