Listas dependentes Excel – Validação em cascata

Veremos como criar caixas de listagem com listas dependentes no Excel, a validação em cascata.

Por exemplo, você tem um cadastro e precisa que o cliente preencha a UF e a Cidade neste cadastro.

Cada UF tem as suas respectivas cidades e você quer que seja preenchido corretamente, por isso deseja permitir que ele selecione apenas as cidades da UF selecionada por ele.

Desta forma o cadastro ficará correto, pois o próprio Excel se encarregará de bloquear entradas de dados incorretas.

Vejamos como fazer isto em qualquer versão do Excel.

Preparar a pasta de trabalho

Para o nosso trabalho nós criamos três planilhas:

• Dados: Nesta planilha nós colocamos as tabelas e listas de dados com informações que são utilizadas na pasta de trabalho.
• Cálculos: Aqui são realizados os cálculos auxiliares.
• Validação: Na planilha de validação, é a parte aonde os dados serão utilizados ao cliente.

Planilha Dados

Temos então duas tabelas, uma com a lista de UF única, vista acima em E9.

E a outra tabela nós temos Cidades e UFs em B9 aonde a lista de UF está classificada, isto é obrigatório para que a técnica funcione.

Planilha Cálculos

Na planilha Cálculos nós temos as fórmulas auxiliares que são utilizadas na planilha.

No caso desta planilha nós colocamos ali a UF que deverá ser filtrada na tabela de Validação.

Este valor em C9 será utilizado nas fórmulas de cálculos. Clique em C9 e altere o nome da fórmula para UFSelecionada.

Fórmulas de Gerenciador de Nomes

Agora precisamos criar os intervalos nomeados auxiliares que serão utilizados nas fórmulas.

Primeiro criaremos no gerenciador de nomes a lista de UF, clique em Novo e Digite UF no nome e em Refere-se a selecione a coluna da Tabela UF conforme na figura.

Este intervalo nomeado será utilizado para criar uma lista de validação com as UFs para o cliente.

Crie um novo intervalo e nomeado como Cidades e preencha com a seguinte fórmula: =DESLOC(Dados!$B$9;CORRESP(UFSelecionada;Tabela3[UF];0);;CONT.SE(Tabela3[UF];UFSelecionada)).

A fórmula funciona da seguinte forma:

=DESLOC(Dados!$B$9;CORRESP(UFSelecionada;Tabela3[UF];0);;CONT.SE(Tabela3[UF];UFSelecionada))

A ideia da função é selecionar a célula inicial da coluna Cidade, esta célula será deslocada conforme o uso da função Corresp que identifica a linha aonde está a UF que está definida em UFSelecionada.

Desta forma a célula B9 é deslocada para a próxima célula aonde está a primeira UF que está listada conforme a que estiver definida na tabela planilha Cálculos.

Depois por fim é alterada a quantidade de registros que existem com a UF selecionada, com a quantidade de estados que existem na tabela de Cidades e UFs.

Utilizando as listas de validação em cascata

Na planilha Validação é aonde realizamos nossos testes.

Para criar a validação de listas dependentes realize o seguinte processo:

Selecione aonde serão escolhidas as UFs e clique em Dados->Validação de dados.

Depois selecione a opção de Validação Listar e digite =UF na fonte.

Veja que ao clicar na célula as UFs são listadas. Agora trataremos das Cidades.

Selecione aonde serão escolhidas as Cidades, na coluna ao lado da UF e clique em Dados->Validação de dados.

Depois selecione a opção de Validação Listar e digite =Cidade na fonte.

Agora a lista de cidades está sendo exibida ao selecionar na célula da lista de Cidades.

Mas a lista de cidades é sempre a mesma, iremos agora tratar de como alterar a UF na planilha Cálculos.

Como alterar a UF na fórmula de Validação em Cascata

Para alterar a UF e fazer com que a lista de Cidades seja alterada pressione ALT+F11 e clique duas vezes sobre a planilha Validação.

Agora utilizaremos um código para que ao mudar a célula o valor em UFSelecionada seja alterado automaticamente em Calculos. Digite o seguinte código VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 3 Then
        Calculos.Range("ufselecionada").Value = Cells(Target.Row, 2).Value
    End If
End Sub

No código acima, nós temos o evento do VBA que identifica se ao alterar a célula na planilha Validação sejam alterados os valores na célula Calculos.Range(“ufSelecionada”).

Este valor é mudado apenas se a Target.Column for igual a 3, ou seja a coluna aonde está a nossa lista de cidades.

Assim, o valor que estiver digitado na coluna de UF irá alterar a lista de cidades automaticamente.

Conclusão listas encadeadas no Excel

Com esta aplicação podemos criar quantas listas de validações precisarmos, funcionando em versões mais antigas no Excel.

Download

Faça o download da planilha de exemplo e para analisar a fórmula preencha o formulário de e-mail e faça o download. E no vídeo no topo do artigo você pode assistir esta explicação.

Baixe a planilha

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: