Menu suspenso do Excel com condição

Question

Já faz algum tempo que procurei sem conseguir encontrar uma solução para o meu problema. A possui uma tabela chamada tbl_lov que contém a lista de valores usados ​​em uma lista suspensa via função indireta. INDIRETO("tbl_lov[ProjNR]")

À medida que os projetos vão e vêm, tenho uma coluna em tbl_lov chamada active que contém um valor True ou False. Se for falso, não quero que esse valor seja mostrado no menu suspenso. Infelizmente, ainda não descobri como posso fazer isso diretamente.

Answer 1

Como parece que não é nada trivial usar apenas as funções, o que acabei fazendo foi usar uma Macro (que eu queria evitar).

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

Isso irá iterar pela minha tabela existente e copiar para um novo intervalo apenas os projetos ativos. A validação de dados aponta para a nova tabela.