Question

我正在寻找一种基于多列命名范围在Excel中筛选列表验证的方法。

I have a list of product releases on one sheet, contained in a named range that has the columns: Name, Type, Status. On another sheet, I want the user to be able to select from a validation list containing Name only. Question 3741060 here covers how to make the validation list only contain the Name column. However I also need to filter so that the user cannot select a release with the status Completed . [The status column only allows Planned , Allocated or Completed .]

Ideally I would also like to dynamically show only Planned OR Allocated releases based on yet another validation - but I think if I can get the list filtered at all I should be able to do the rest. BTW - I am forced to use Excel 2003 for this, although I don t believe would be a major factor.

Answer 1

我使用

an extra range LOV (for List of Values) in a hidden sheet that I fill with the current criteria the user can choose from (in my case this varies from line to line as he/she fills the sheet)
all cells in the main sheet are validated against this range LOV
a Selection_Change() trigger loads the LOV after each cursor move from the original range of possible choices

这就是我重新生成LOV的方式（本质上，用户已经在另一个单元格中选择了一个国家代码，该单元格以字符串CtyCd传递到这里，现在该表准备提供一个名为GINI的可能选项，仅适用于这个国家……所以可能与您的需求类似）

Sub LoadL2LOV(CtyCd As String, LOVL2 As Range)
 
  CtyCd is a selection criterium for the original list in range GINI
  LOVL2 is the target range containing the current list of values
  all cells in sheet have a validation against range LOV defined
 
Dim GINI As Range, Idx As Long, Jdx As Long, LName As Name, Adr As String

      clear current PoP_L2
    Set LName = ActiveWorkbook.Names(LOVL2.Name.Name)
    Set GINI = Worksheets("GINI Availability").Range("GINI")
    LOVL2.ClearContents

      set new LOV for PoP_L2
    If CtyCd <> "" Then
        Idx = 2
        Jdx = 1

          find 1st occurence of CtyCd in GINI
        Do While GINI(Idx, 4) <> CtyCd And GINI(Idx, 4) <> ""
            Idx = Idx + 1
        Loop

          GINI is sorted, just read until the end of selected CtyCd
        Do While GINI(Idx, 4) = CtyCd
            LOVL2(Jdx, 1) = GINI(Idx, 1) & "-" & GINI(Idx, 2) & "-" & GINI(Idx, 3)
            Idx = Idx + 1
            Jdx = Jdx + 1
        Loop
    End If

      redefine LOV name to contain all current valid choices
    LOVL2.CurrentRegion.Name = LOVL2.Name.Name
End Sub

在您的情况下，由于数据似乎或多或少是静态的，您可以在Sheet_Activate或任何适当的激活触发器处将所有有效选择从[Produd_Release]复制到LOV。

希望这能有所帮助。。。。祝你好运MikeD

友情链接