Filtering a validation list based on columns within a named range

I am looking for a way to filter a list validation in Excel based on a multi-column named range.

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.

I use

• 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

This is how I re-generate my LOV (in essence the user has already selected a country code in another cell passed here in string CtyCd, and the sheet now is prepered to offer a selection of possible choices of something called GINI for only this country ... so maybe similar to your demand)

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

In your case, as the data seems to be more or less static, you can copy all valid selections from [Prod_Release] to LOV at Sheet_Activate or any appropriate activation trigger.

Hope this helps .... good luck MikeD