Excel dropdown with condition

Question

I have searched for a while without being able to find a solution to my problem. A have a table named tbl_lov that contains the list of values used in a dropdown list via indirect function. INDIRECT("tbl_lov[ProjNR]")

As projects come and go, I have a column on tbl_lov named active that holds a value of True or False. If it is false I do not want this value to be shown in the dropdown. Unfortunately, I have yet not figured out how I can do this directly.

Answer 1

As it seems that is nothing trivial by using just he functions, what I ended up doing was using a Macro ( that I wanted to avoid).

Dim all_lov As Variant
Dim row_nr As Integer

Sub Button1_Click()
    Worksheets("LoV").Range("I:N").Delete
    row_nr = 3
    all_lov = Worksheets("LoV").ListObjects("tbl_lov").DataBodyRange.Value
    Worksheets("LoV").Range("B3:G3").Copy Worksheets("LoV").Range("I3")
    For i = 1 To UBound(all_lov)
        If all_lov(i, 6) = True Then
            row_nr = row_nr + 1
            Worksheets("LoV").Range("B" & (3 + i) & ":G" & (3 + i)).Copy Worksheets("LoV").Range("I" & row_nr)
        End If
    Next i
    Worksheets("Lov").ListObjects.Add(SourceType:=xlSrcRange, Source:=Worksheets("LoV").Range("I3:N" & row_nr), xlListObjectHasHeaders:=xlYes, tablestyleName:="TableStyleDark3").Name = "tbl_lovActive"
End Sub

This will iterated thourgh my existing table and copy to a new range just the active projects. The data validation points to the new table.