Validação de dados com lista variável e tamanho variável no Excel

Validação de dados com lista variável . Você já teve a necessidade de criar uma lista baseada em outra lista? Por exemplo, uma lista aonde você tenha as unidades federativas (estados) e na outra as cidades daquela cidade na mesma linha?

Este artigo demonstra como criar uma validação de dados com lista variável e tamanho variável no Excel, ou seja, criar uma lista de dados baseada em um critério, como o estado, e listar os seus itens ajustando inclusive a quantidade de registros exibidos.

Vamos ver como criar passo-a-passo esta lista.

1. Crie 3 planilhas:
   • Cadastro: aonde criaremos um cadastro simples de clientes;
   • Base: servirá como base para a lista de cidades e estados;
   • Cálculos: realizaremos cálculos para que a lista de cidades seja criada nesta planilha.
2. Na planilha Cadastro crie um cadastro simples conforme a imagem, digitando apenas os nomes, não se preocupe com a formatação

   

3. Selecione a lista até a linha 2 e selecione em Página Inicial a opção Formatar como tabela, selecionando um estilo e marque a opção minha tabela contém cabeçalhos. Automaticamente a planilha já será configurada.
4. Na coluna Id utilize a seguinte fórmula =lin()-1, para que seja gerado um cadastro um novo código de cliente á medida em que seja inserida nova linha.
5. Clique em Base e digite a lista de UFs e na célula A1 digite as UFs seguintes:[table id=3 /]
6. Na célula D1 digite a lista de cidades do Brasil, baixe no link abaixo a lista:

Na célula C1 digite a função =D1&"-"&CONT.SE($D$1:D1;D1) e arraste para todas as linhas, esta função irá criar uma chave única para todas as cidades, de modo que possamos usar o PROCV para retorná-los em cálculos.

Na planilha Cálculos digite em A1 SC, na célula B1 digite 1, na B2 digite 2, selecione B1 e B2 e arraste a seleção até a linha B700 de modo que tenhamos uma lista sequencial.

Na célula C1 digite a função =SEERRO(PROCV($A$1&"-"&B1;Base!C:F;4;0);1), esta função irá realizar uma busca das cidades conforme o Estado selecionado. Arraste esta fórmula até a linha C700.

Clique na guia Fórmulas e no botão Gerenciador de Nomes

Clique no botão Novo, nomeie como Estado e digite a fórmula =DESLOC(Base!$A$1;0;0;CONT.VALORES(Base!$A:$A);1) esta fórmula irá retornar a lista de estados.

Clique novamente na guia fórmulas em Gerenciador de nomes e no botão Novo

Nomeie-o como Cidade e digite a fórmula =DESLOC(Cálculos!$C$1;0;0;CONT.SE(Cálculos!$C$1:$C$700;"<>1");1) esta fórmula irá retornar as cidades conforme o estado digitado em A1 na planilha Cálculos.

Clique na planilha Cadastro e nas células abaixo da coluna UF, clique na guia Dados na opção Validação de Dados.

Selecione a opção Lista e em Fonte digite =Estado. Isso fará com que neste campo sejam exibidos todos os Estados para seleção.

Selecione as linhas abaixo da coluna Cidade

Clique na guia Dados e na opção Validação de dados

Selecione a opção Lista e em Fonte digite =Cidade

Agora habilite a guia Desenvolvedor e clique em no botão Visual Basic

Clique duas vezes sobre a Planilha 1 (Cadastro) e digite o seguinte código:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 5 Then
        Sheets("Cálculos").Range("a1").Value = Range("D" & Target.Row).Value
    End If
End Sub

Este código fará com que ao selecionar uma célula da coluna 5, o sistema automaticamente altere o valor da célula Cálculos!A1 para a UF da linha atual, mudando toda vez que for alterada a linha.

Abraço

Marcos Rieper

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: