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

5

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çãoValidaçã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. DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

    Seu nome (obrigatório)

    Seu e-mail (obrigatório)

  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

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

Seu nome (obrigatório)

Seu e-mail (obrigatório)

Abraço

Marcos Rieper

Marcos Rieper
Casado, 35 anos, analista de sistemas e administrador de empresas. Criou o Guia do Excel em 2010 com o objetivo de auxiliar as pessoas no uso da ferramenta indispensável em qualquer escritório do mundo.

5 COMENTÁRIOS

  1. Olá! Sou mesmo um iniciante em Excel, ou melhor, era, até acessar aqui. Muito bom mesmo. Já estou até ajudando algumas pessoas a fazer algumas “coisinhas” no Excel. kkkkkkk Pode um negócio desse!!!

  2. Muito bom mesmo. Comecei procurando uma curiosidade primária minha, sobre Excel. Achei um vídeo com a responsabilidade de Marcos Rieper. Pronto!!! Me identifiquei com a fala deste… desde então é um vídeo após o outro, é claro que alguns videos, como esse último (estados/cidades) uma tabela, tive que voltar várias vezes, é lógico. Mas hoje tenho minha próprio tabela, que há de ser muito útil. Enfim, de alguém que estava procurando somar/subtrair células, eu estou desenvolvendo planilhas na área de avançadas. Valeu Sr Marcos Rieper.

  3. Boa tarde, Marcos.

    É possível estender este método? Isto é, por exemplo, seleciono o estado numa célula, ele atualiza a lista de cidades na seguinte e numa terceira célula, a partir da cidade selecionada ele atualizar uma lista de bairros?

    Grato.

DEIXE UMA RESPOSTA

Please enter your comment!
Please enter your name here