Excel Como fazer lista com dados mesclados/limitados, só que com muitas opções?

[Esraeu]

Olá.

Tentarei ser o mais claro possível, é meio difícil de explicar.

 

Eu preciso fazer uma daquelas listas suspensas de validação de dados em que se uma célula tiver com um certo texto, essa lista mostre apenas alguns resultados. Eu até consegui fazer, mas a fórmula fica muito grande e passa do limite do Excel, aí eu preciso de uma maneira mais inteligente de fazer isso.

 

Exemplo: essa planilha tem o modelo e o tamanho:

 

A célula C2 é o valor do modelo que tem uma lista que puxa os valores de E3 até E10:

 

Quando eu seleciono um modelo na célula C2, a lista do Tamanho fica limitada conforme o modelo:

Nesse caso ele leu que o modelo é YY2 e mostrou as opções de G8 até G11, por causa de uma fórmula em que fiz.

 

É exatamente isso que eu quero fazer, o problema é que a fórmula fica grande demais:

=SE($C$2="XX1";$G$3;SE($C$2="YY1";$G$4:$G$7;SE($C$2="YY2";$G$8:$G$11;SE($C$2="YY3";$G$12:$G$15;SE($C$2="YY4";$G$16:$G$19;SE($C$2="YY5";$G$20:$G$23;SE($C$2="YY6";$G$24:$G$27;$G$28:$G$31)))))))

Com todas as opções que eu preciso, essa fórmula fica tão grande que o Excel não permite. Tentei fazer com PROCV e não consegui.

 

Alguém sabe como fazer isso de uma maneira mais "compacta"?

[Midori]

@Esraeu Pode ser com macro? Assim poderia adicionar uma nova lista com os tamanhos na célula C3 toda vez que o modelo é selecionado.

[Esraeu]

@Midori O que seria macro? Isso é daquela parada de opções do desenvolvedor?

Nunca mexi com macro antes, nem sei o que é. Se não for tomar muito de seu tempo, estou disposto a aprender.

[Midori]

@Esraeu Podemos ativar a macro para cria lista quando a célula B2 é modificada.

 

Estes são os passos:

 

1) Clique com o direito na aba da planilha e selecione Vew Code:

 

 

2) No editor cole o código. Depois a planilha deve ser salva como xlsm.

 

 

Sub AtualizaLista(Lista As Range, AreaModelo As Range, Modelo As String)
    Dim Area    As Range
    Dim Procura As Range
    Dim Valores As String
    
    Set Procura = AreaModelo.Find( _
        What:=Modelo, LookIn:=xlValues, LookAt:=xlWhole)
    
    If Not Procura Is Nothing Then
        Set Procura = Procura.MergeArea
        Set Area = Procura(1, 2).Resize(Procura.Rows.Count)
        
        If Area.Rows.Count = 1 Then
            Valores = Area.Value
        Else
            Valores = Join(Application.Transpose(Area), ",")
        End If
        
        Call Lista.Clear
        Call Lista.Validation.Delete
        Call Lista.Validation.Add(Type:=xlValidateList, Formula1:=Valores)
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$2" Then
        Call AtualizaLista([B3], [F:F], [B2])
    End If
End Sub

 

[Esraeu]

@Midori Funcionou! Muito obrigado!

 

Só tive que trocar de B para C no código, pois era na coluna C.

Vi que quando seleciona o modelo a formatação daquela célula é alterada:

Mas isso deve ter como formatar por lá.

 

Mas é bem interessante como o Excel permite fazer algo assim, um dia vou dedicar tempo para entender como fazer coisas assim.

[Midori]

@Esraeu A macro tira a formatação porque usei Clear, para deixar é só substituir por ClearContents,

 

Call Lista.ClearContents

 

Ou então remova essa linha se não quiser apagar a última seleção.

[Esraeu]

@Midori Funcionou, ajudou mesmo. Muito obrigado.

Fiz o teste adicionando mais valores e funcionou, é exatamente o que eu precisava.