Lista suspensa com condicional
Este artigo é uma sugestão do Raphael, um dos leitores do Função Excel que entrou em contato conosco e deixou algumas ideias de coisas que ele costuma utilizar no dia-a-dia dele e achou que seria interessante ter por aqui.
A ideia aqui é criar uma lista suspensa cujo os itens desta lista possam ser diferentes de acordo com algum outro item. Simplificando, é feito um teste para verificar se o conteúdo de alguma célula é verdadeiro ou não, caso positivo, então é exibida uma lista, caso falso, é exibida uma outra lista diferente.
Se você não sabe o que é uma lista suspensa, clique aqui e saiba mais.
Exemplo prático
Tudo fica mais fácil em um exemplo, então vamos lá.
Imagine que tenhamos 4 equipes diferentes, com integrantes diferentes e precisamos criar uma lista suspensa com base no nome da equipe selecionada. Assim, quando o nome de uma equipe for selecionada a lista ira exibir somente os componentes desse.
Na célula G4 será incluída uma lista suspensa comum, que irá buscar o nome das equipes. É ela que ira guiar a lista suspensa com condicional, que será incluída na célula H4.
A imagem abaixo mostra de forma rápida como essa lista foi feita. Para maiores detalhes clique aqui.
Para a criação da lista suspensa com condicional é necessário um passo anterior, que é definir um nome. Este processo permite criar um objeto que possuem um comportamento específico, por exemplo, uma formula, e utilizá-la depois em outro lugar apenas informando o Nome.
Siga os passos da imagem.
Assim que clicar no botão novo, será aberta a janela abaixo.
No campo Nome você pode escrever o que quiser, escolhi usar Lista.
No campo Escopo você irá definir se este nome será válido para toda a pasta de trabalho ou somente para uma planilha (aba).
Em Refere-se a é que mora o segredo, aqui você pode selecionar uma única célula, um conjunto de células ou uma fórmula. Para este caso usaremos uma fórmula. Que segue:
=DESLOC(Equipes!$B$3;1;CORRESP(Equipes!$G$4;Equipes!$B$3:$E$3;0)-1;4)
Indo de argumento em argumento teremos:
Equipes! -> Se refere ao planilha em que estamos trabalhando.
Equipes!$B$3 -> A célula onde iniciam as listas com os nomes dos integrantes de cada equipe.
1 -> Indica que o resultado será deslocado em 1 linha a partir da célula Equipes!$B$3.
CORRESP(Equipes!$G$4;Equipes!$B$3:$E$3;0)-1 -> Identifica em qual posição está o nome da equipe que consta na célula Equipes!$G$4. Já dentro da função DESLOC esta parte se refere ao número de colunas que o resultado será deslocado, ou seja, para equipe Beta o resultado da função CORRESP será 2 (e depois subtraí-se 1) indicando a função DESLOC que deverá deslocar 1 coluna em relação a célula Equipes!$B$3.
4 -> Indica a altura ou a quantidade de integrantes que cada equipe possui. Este número poderia ser maior e as equipes não necessariamente precisariam ter o mesmo número de integrantes.
A parte mais difícil já está pronta, agora só precisamos criar a lista suspensa na célula H4.
Selecionamos a célula onde queremos colocar a lista suspensa, neste caso a H4 e seguimos os mesmos passos exibidos anteriormente quando montamos a lista suspensa para o nome da equipe, a diferença é que agora ao invés da Fonte ser um conjunto de célula será o Nome que acabamos de criar.
Se você seguiu todos os passos, tudo deve estar funcionando perfeitamente.
Acompanhe o Função Excel
Olá, gostaria de saber como finalizaria a fórmula, se as equipes não tivessem o mesmo número de integrantes?
Para utilizar equipes com quantidades diferentes de integrantes, basta colocar um número equivalente ao numero máximo de integrantes possíveis.
Muito boa essa dica!!!!
Bom dia,
Segui todos os passos mas quando vou finalizar aparece a mensagem:
“A fonte atualmente resulta em erro. Deseja continuar?”
Quando clico em sim e vou fazer o teste, as listas suspensas não aparecem. Só aparece a “setinha”, mas não aparece nenhuma opção para seleção.
Sabe me dizer por que ocorre isso?
O meu esta acontecendo a mesma coisa. Fiz exatamente o exemplo que aparece aqui para reproduzir, mas aparece o mesmo erro.
Descobri um erro. A função Desloc recebe 5 argumentos: referencia, linhas, colunas, altura e largura. No exemplo acima está faltando a largura, que no caso, como é uma unica coluca que queremos por vez, é 1. Observe que colocando o 1 no final da certo
=DESLOC(Equipes!$B$3;1;CORRESP(Equipes!$G$4;Equipes!$B$3:$E$3;0)-1;4;1)
Realmente, com a sua alteração funciona perfeitamente!
Obrigada.
tem como fazer uma lista apenas escrevendo ela na linha caixa de texto “fórmula” (onde nós adicionamos as fórmulas e funções)?
Olá! Tentei seguir suas instruções, mas, quando vou usar o Nome como Fonte para a Validação de Dados, aparece a seguinte mensagem: “A fonte de lista deve ser uma lista delimitada ou uma referência a uma única linha ou coluna”. Você poderia me ajudar? Adaptei sua fórmula para o meu caso concreto, com referência a duas abas diversas – será esse o problema? Muito obrigada!
Como fazer essa formula no google sheet?
Ola. Para mim funcionou. Porém, eu preciso que isso seja aplicado em diversas linhas (quantas forem necessárias). Verifiquei que somente arrastar nao adianta. Existe algo tipo active cell -1, para dizer que eh a célula esquerda de onde o drop down está?obrigada
Acho que deve usar o $ para “travar” a coluna ou linha desejada
Olá. Experimente “soltar” a linha 4 que está “travada”. Ficaria assim: Equipes!$G4.