Remover valores usados de listas de seleção
Imagine que você possui uma série de listas de seleção criadas com validação de dados, e que todas estas seleções têm como base a mesma lista e que em cada uma delas você precisa informar um valor diferente da outra. Não seria muito mais fácil se a cada item selecionado, este item sumisse das demais listas de seleção? Bom, é isso que você vai aprender agora!
O vídeo abaixo explica tudo, tim-tim por tim-tim, mas se preferir pode continuar lendo mais abaixo o passo-a-passo.
Coloque o vídeo em tela cheia para assistir normalmente
A lista Original
Partindo da lista contida na coluna B da imagem abaixo, iremos criar duas listas de seleção nas células G3 e H3 que exibirão apenas os valores ainda não selecionados. Ou seja, se Abelha for selecionado em G3, então deixará de aparecer na lista de H3.
Fórmula de Controle
Está é a fórmula que controlará se um item já foi selecionado, ou não.
A fórmula a ser inserida na célula C3 e então copiada até a célula C8 é:
=SE(CONT.SES($G$3:$H$3;B3)>=1;””;LIN(B3))
Onde:
CONT.SES($G$3:$H$3;B3)>=1 ⇒ Irá verificar se a palavra contida em B3 já foi selecionada em uma das listas de seleção $G$3:$H$3.
Caso o resultado dessa contagem for maior ou igual 1, a função SE irá retornar vazio (“”), caso contrário, irá retornar a linha atual com a função LIN(B3).
Fórmula da Lista Final
Esta fórmula pegará a Lista Original e irá comparar com o Controle exibindo somente os itens que possuem número em Controle, ou seja, que não estão em branco no controle e que portanto, ainda não foram selecionados.
A fórmula que deve ser inserida na célula D3 e copiada até a célula D8 é:
=SEERRO(ÍNDICE(B:B;MENOR($C$3:$C$8;LIN(B3)-3+1););””)
Onde:
MENOR($C$3:$C$8;LIN(B3)-3+1) ⇒ Irá identificar qual é o dado da Lista Original que deve ser buscado. O número 3 indica a linha em que a lista começa. O resultado de LIN(B3)-3+1 será igual a 1 indicando à função MENOR que deve buscar o primeiro menor valor contido no Controle. Caso Abelha já tenha sido selecionado, esta estará em branco no Controle, e o menor valor do controle será o referente a Banana.
ÍNDICE(B:B;MENOR($C$3:$C$8;LIN(B3)-3+1);) ⇒ A função ÍNDICE irá apenas buscar o item contido na coluna B, Lista Original, na linha indicada pela função MENOR.
SEERRO(ÍNDICE(B:B;MENOR($C$3:$C$8;LIN(B3)-3+1););””) ⇒ Caso ocorra algum erro na função ÍNDICE, a função SEERRO fará com que o resultado seja igual a vazio (“”).
Está praticamente pronto, na verdade a nossa lista já funcionaria da maneira que está, porém sempre que um item já tiver sido selecionado, nossa lista de validação de dados irá exibir um espaço em branco, ao invés de completamente excluir aquela entrada. Para resolver isso precisamos fazer mais uma formula.
Fórmula da Área da Lista
A formula a ser inserida na célula E3 é:
=”ListaDinâmica!D3:D”&CONT.NÚM($C$3:$C$8)+2
Onde:
ListaDinâmica ⇒ É o nome da aba do Excel em que estamos trabalhando.
CONT.NÚM($C$3:$C$8)+2 ⇒ Conta quantos número aparecem no Controle, ou seja, quantas entradas devem aparecer na lista. O +2 é por conta da lista iniciar na linha 3.
Esta fórmula dirá que a Área da Lista será da célula D3, onde se inicia a Lista Final, até uma célula da coluna D que será encontrada de acordo com o número de item na Lista Original e quantos deles já foram selecionado.
Pronto, já temos a Área da Lista e a Lista Final. Basta criar nossas listas de validação de dados, mas como estas precisarão entender o que está escrito na Área da Lista, na célula E3, faremos um último passo, que é criar um nome para esta célula, o qual usaremos na criação da lista de validação de dados.
Definindo um nome para a lista
Siga os passos das imagens abaixo para definir um nome.
A fórmula inserida no campo Refere-se a é:
=INDIRETO(ListaDinâmica!$E$3) ⇒ Célula onde está contida a Área da Lista
Criando a lista de validação de dados
Tudo pronto, agora é só criar as listas, selecione as células G3 e H3 e siga os passos das imagens:
PRONTO!
Veja que agora ao selecionar um item em uma das listas, ele já não aparece mais na outra.
Acompanhe o Função Excel
Estou necessitando de uma fórmula no excel, que a solução que você mostrou nesse post vai me ajudar. Fiz tudo do jeito que você explicou e está apresentando os seguintes problemas:
1 – A fórmula que disse para inserir na célula E3, =”ListaDinâmica!D3:D”&CONT.NÚM($C$3:$C$8)+2, não funciona.
2 – Quando seleciono por exemplo, a palavra CASA na célula G3, é apresentado o termo #NOME?, na célula C3 e em todas as células da coluna D. O mesmo ocorre com outras palavras. Poderia me ajudar a resolver? Ou que sabe, enviar para o meu email, a planilha com as fórmulas funcionando corretamente? Obrigado
Oi, Flávio.
Eu também tive esse problema 1 com relação a =”ListaDinâmica!D3:D”&CONT.NÚM($C$3:$C$8)+2 e isso acarretou em erro no restante do processo. Acontece que o erro foi nosso por copiar e colar as funções. Assim, o Excel não reconhece as aspas corretamente. Bastou redigitar todas as aspas de todas as funções do exemplo que funcionou aqui pra mim.
Espero ter ajudado.
Obrigado ao autor do exemplo. Era justo o que eu procurava.