Excel na Web
Coloque seu e-mail aqui para receber GRATUITAMENTE o meu EBOOK →

→ 10 Funções que podem fazer de você um perito em EXCEL

32 Como fazer listas dependentes no Excel

Como adicionar diferentes listas suspensas na mesma célula no Excel. A validação de dados do Excel permite criar uma lista suspensa, Drop Down List, para restringir os valores inseridos. Com um pouco de criatividade é possível definir diferentes listas suspensas em uma mesma célula. O uso de intervalos nomeados em conjunto com a Função Indireto permite criar listas dependentes no Excel e adicionar flexibilidade para troca das listas de forma simples e prática.

Selecionar produto de acordo com a categoria

Na figura abaixo existe na Coluna A uma lista suspensa com as categorias de produtos existentes.  A lista de categorias é preenchida pelo método tradicional da validação de dados. Para criar uma lista como esta no Excel leia:


Após selecionar uma categoria, o Drop Down List da Coluna B será preenchido com os produtos que a ela pertencem.


Para obter este resultado é necessário utilizar a Função INDIRETO no campo Fonte da validação de dados.


Para que a Função INDIRETO possa trazer os produtos pertencentes a cada categoria, o ideal é adicionar uma “Planilha Auxiliar” que armazene a relação de produtos divididos por categoria. Então deve-se nomear o intervalo de cada lista de produtos com o nome da categoria, como na figura abaixo:



É proibido a reprodução parcial ou integral dos artigos deste blog. Copiar na íntegra qualquer conteúdo aqui publicado, implica em crime previsto no Código Penal

Agora é com você!


Se você gostou, compartilhe na sua rede social: Facebook, Twitter ou Google+ usando os botões abaixo, isso ajuda a incentivar o trabalho.

Ricardo Fialkoski é o fundador do blog Excel na Web. Trabalha com Excel há mais de dez anos desenvolvendo Soluções Avançadas em Excel e VBA.

32 comentários :

  1. Boa noite,


    Seu blog e ótimo!!!

    Uma dica.

    Poderia colocar um passo a passo como inserir via (gerenciador de nome) ensinado como alterar, incluir, excluir a lista de nomes.

    ResponderExcluir
    Respostas
    1. Muito obrigado! É fantástico saber disso! Excelente dica, já anotei aqui e vou preparar um conteúdo bem completo a respeito. Continue acompanhando o blog e deixe um comentário sempre que puder! Obrigado por todo o apoio!

      Excluir
  2. Muito Bom. Faltou apenas o passo a passo de como inserir para os mais leigos como eu.

    ResponderExcluir
    Respostas
    1. Obrigado Raimundo, vou providenciar o passo a passo, assim que ficar pronto atualizo este tutorial. Um abraço!

      Excluir
  3. Muito Obrigado.
    Mas a dúvida é a seguinte: Como definir nomes com "ESPAÇO"? Pois preciso que fique aparecendo o nome Ex. OI TV, só consigo fazer OI_TV para definir o nome.

    ResponderExcluir
    Respostas
    1. Ola Lipe, o Excel não permite utilizar espaço para nomear um intervalo. Existe uma forma de fazer isso usando a Função Procv em conjunto com a Função Indireto. Em breve escreverei a respeito. Inscreva-se no blog e eu te aviso quando ficar pronto ok? Obrigado pela visita!

      Excluir
  4. Lipe é possível trabalhar com espaços utilizando a formula "=SUBSTITUIR()". Vou utilizar o seu caso como exemplo:

    Supomos que a caixa de seleção do item "OI TV" esteja na célula "A1", você deve criar o nome da lista sem o espaço como, por exemplo: "OI_TV".

    Daí você utiliza o seguinte artifício =INDIRETO(SUBSTITUIR(A1;" ";"_"))

    A fórmula SUBSTITUIR() vai converter o nome "OI TV" em "OI_TV" e conseguir trazer a lista desejada.

    Espero que tenha ajudado. Caso tenha dúvidas, acessa o tutorial:

    http://www.contextures.com/xlDataVal02.html

    Um Abraço!
    Everson Lima

    ResponderExcluir
    Respostas
    1. OBRIGADO, TU É UM ANJO! ESTAVA ME DEBATENDO NISSO, EM DESLOC, CORRESP, PROCV! SEMPRE PREFIRO O MAIS SIMPLES VALEU

      Excluir
  5. Ola, tenho uma duvida,estou criando uma planilha com listas dependentes, porem para cada linha é preciso manualmente o "indireto", pois quando seleciono um intervalo, o resultado da lista dependente sempre retorna a primeira linha e não as abaixo.
    Ex.: tenho uma lista suspensa na coluna A no intervalo de 1 à 10 e criei a lista dependente na coluna B, tambem de 1 à 10, mas quando retorna o resultado nas linhas 2,3,4... sempre aparece o valor da linha 1. Somente fazendo linha por linha da certo, há uma outra forma de selecionar todas as linhas que deseja retornar os resultados desejados?

    ResponderExcluir
    Respostas
    1. Boa noite, infelizmente você tem que repetir o "indireto" linha a linha, porém se as listas forem dependentes de uma mesma lista você pode copiar e colar a lista de validação. Você baixou o meu exemplo? Um abraço!

      Excluir
  6. Olá bom dia! Tenho uma dúvida. Estou a criar uma planilha em Excel e gostaria da vossa ajuda. Por exemplo: Se na célula A1 colocar por exemplo: "Pratica desporto" e ter resposta "sim" e "Não", gostaria de saber como faço para aparecer a lista de desportos se responder "Sim"?
    Ou seja: Célula A1 = Pratica Desporto? Resposta, Sim/Não
    Na célula B1 (Tipo de desporto), criar a seguinte lista: basketball, futeball, ténis, andeball, etc...etc...sendo que, ao responder "Sim" na célula A1, a célula B1 activa automáticamente e me dar opções de escolha do tipo de desporto e se responder "Não", a célula B1 fica inactiva.
    Espero ter explicado bem e me tenha feito entender.
    Conto com o vosso apoio. obrigado!

    ResponderExcluir
    Respostas
    1. Olá, uma saída é você criar um intervalo nomeado para Sim e outro para Não. O intervalo "Sim" deve compreender as modalidades de esporte e o outro intervalo deixa em branco. Aí é só por em prática a explicação deste tutorial. Entre em contato comigo através deste formulário que eu te envio um exemplo que fiz aqui pra você. Link do formulário de contato: http://www.excelnaweb.com.br/p/blog-page_18.html

      Excluir
    2. Em tempo de pandemia e fuçando o Excell. Muito legal mas, sugiro que as sejam transformadas em tabela, pois
      ao adicionar ou retirar um item, figurará, sem precisar modificar o
      intervalo do nome. ok

      Excluir
  7. Olá Ricardo,

    Existe a possibilidade de uma lista suspensa depender de duas células simultaneamente? Tipo =INDIRETO(A1);INDIRETO(A2)???

    ResponderExcluir
    Respostas
    1. Oi Philipe acredito que só seja possível fazer isso de forma automatizada com VBA. Pensando aqui rapidamente não consegui pensar em funções que pudessem ser aplicadas sem VBA. Abraços!

      Excluir
  8. Ótimo, só faltou atualizar o link da leitura complementar (Como criar intervalos nomeados no Excel). Abraço.

    ResponderExcluir
    Respostas
    1. Obrigado por avisar Dajano! Já corrigi o link para a leitura complementar que mostra como usar e nomear intervalos no Excel. Abraços!

      Excluir
  9. ola, tudo bem?
    parabéns pela sua aula...me ajudou muito a esclarecer a criação de listas suspensas...
    mas tenho uma dúvida, peço que me ajude se puder...

    exemplo
    tenho uma planilha (plan2), na celula C1 eu tenho NOMES DE CONTATOS
    na celula C2 eu tenho ENDEREÇOS
    tenho uma planilha (plan1) com esses dados nas colunas A e B
    a minha duvida é como fazer para que apareçam essas listas sincronizadas entre si ou seja, o nome que eu escolher, automaticamente aparece o seu endereço, sem ter q procurar manualmente...

    preciso que me ajude
    meu email é belmazza78@gmail.com

    agradeço muito

    Isabel

    ResponderExcluir
    Respostas
    1. Ola! Você consegue buscar o nome e trazer o endereço com a Função PROCV do Excel. De uma olhada neste post: http://www.excelnaweb.com.br/2012/02/funcao-procv.html

      Abraços!

      Excluir
  10. Bom artigo, Ricardo! Parabéns!!!

    Estou precisando nomear um intervalo com espaços (Nomes de Unidades escalares: C. E. Xxxxx, E. M. YYYY, Centro Integrado de Educ. Zzzz)

    Como proceder? Abçs

    ResponderExcluir
    Respostas
    1. Tiago neste caso você precisa usar um recurso mais complexo para obter este resultado, existem algumas formas de realizar esta operação sendo uma delas usar a Função PROCV como argumento da Função INDIRETO.

      Excluir
  11. Como faço para incluir mais um subitem? Exemplo:
    Classificação 1; Classificação 2; Classificação 3, sendo cada uma dependente da outra

    ResponderExcluir
    Respostas
    1. Olá! Em primeiro lugar você precisa criar uma coluna na Planilha Auxiliar com os itens que compõem a Classificação 3. Feito isso você precisa usar a validação de dados como explicado neste tutorial mas ao invés de usar a fórmula INDIRETO(A2) precisa referenciar a célula que contem a classificação 3 por exemplo INDIRETO(B2). Abraços!

      Excluir
  12. Boa noite! amigo parabens pelo site esta ótimo.
    Gostaria de algo diferente do exemplo acima, procurei e não encontrei.
    EX: ao selecionar a categoria no menu (a2), a celula (b2) preenchece automatico com 1 resultado. sem a necessidade de clicar no menu e escolher. É Possivel ? Obrigado e sucesso.

    ResponderExcluir
    Respostas
    1. Olá Adilson! Para obter este resultado você precisa usar a Função PROCV junto com a validação de dados. Basta você inserir na célula B2 a uma fórmula procv que procure o valor de A2, no entanto você precisa criar uma tabela auxiliar com todas as informações necessárias.

      Excluir
  13. Eu uso validação com listas dependentes em vários níveis. Ex. Regiões/Estados/Cidades Brasileiras. Link:
    https://drive.google.com/file/d/0BzlZbEfQYO8ebVZ4N2YyZXduLW8/view?usp=sharing
    Um Abraço, Paulo Caetano
    Sete Lagoas, MG

    ResponderExcluir
    Respostas
    1. Atualização da planilha de validação. Mesmo link anterior:https://drive.google.com/file/d/0BzlZbEfQYO8ebVZ4N2YyZXduLW8/view?usp=sharing
      Pode usar, divulgar mas cite meu nome.

      Excluir
    2. Olá, Paulo!
      Muito obrigado por sua contribuição!
      Forte Abraço!

      Excluir
  14. Amigo. Bom dia! Gostaria de tirar uma dúvida. Utilizei a função para o uso de seleção de meses do ano e a sublista com os dias referentes ao mês. Acontece que vamos supor que eu selecione Janeiro e a sublista carregue do dia 01 ao dia 31. Aí eu seleciono o dia 31. Caso eu altere o mês de Janeiro para Fevereiro, a sublista não "limpa" a célula que contém o dia 31. Ela mantém o dia 31 na sublista. Existem alguma forma de "limpar" como se ele recarregasse os dias referentes ao mês de fevereiro deixando-a em branco para que eu pudesse escolher o dia desejado?

    ResponderExcluir
    Respostas
    1. Bom dia Vitor, se você seguiu o exemplo deste artigo você criou uma tabela auxiliar com uma coluna para cada mês com seus respectivos dias correto? Ao trocar de Mês o campo que contém a validação de dados da sublista não se atualiza automaticamente. Porem se você trocar para Fevereiro e clicar na sublista verá que ela terá 28 dias. O fato é que esta seleção terá que ser manual.

      Abraços!

      Excluir
  15. Adorei! Já tinha visto tantas dicas mais complicadas e não resolvia meu problema. Agora sim! Sugiro incluir como utilizar a fórmula de validação em várias células na mesma coluna. Testei expandir (acho q é isso) e deu certo.

    ResponderExcluir
    Respostas
    1. Muito obrigado pelo seu comentário Renata!

      Anotei aqui a sua sugestão.Sempre que puder e desejar, deixe um comentário aqui no blog, combinado?

      Eles são meu maior indicativo de que estou caminhando na direção correta.

      Forte Abraço e sucesso!

      Excluir

Comentário(s):