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

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
    Validação de dados com lista variável e tamanho varíavel no Excel 1
  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:
  7. Baixe a planilha
  8. 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.
    Validação de dados com lista variável e tamanho varíavel no Excel 2
  9. 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.
  10. 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.
  11. Clique na guia Fórmulas e no botão Gerenciador de Nomes
  12. 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.
    Validação de dados com lista variável e tamanho variável no Excel 3
  13. Clique novamente na guia fórmulas em Gerenciador de nomes e no botão Novo
  14. 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.
  15. Clique na planilha Cadastro e nas células abaixo da coluna UF, clique na guia Dados na opção Validação de Dados.
  16. Selecione a opção Lista e em Fonte digite =Estado. Isso fará com que neste campo sejam exibidos todos os Estados para seleção.
    Validação de dados com lista variável e tamanho variável no Excel 4
  17. Selecione as linhas abaixo da coluna Cidade
  18. Clique na guia Dados e na opção Validação de dados
  19. Selecione a opção Lista e em Fonte digite =Cidade
  20. Agora habilite a guia Desenvolvedor e clique em no botão Visual Basic
  21. 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.

Estados e cidades Excel
Baixe a planilha

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:


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel