Filtrar dados na validação conforme digitação

Quer contribuir com a manutenção do blog, para que eu possa inserir mais dicas, planilhas e apostilas gratuitamente?

Quer inserir o logotipo da sua empresa nas apostilas e nos arquivos de download?

Telefone: (27) 9275-5331

E-mail: paulosemblano@hotmail.com

Quando a lista da Validação de dados é grande, muitas vezes precisamos filtrá-la conforme a nossa necessidade.
Por exemplo, digitando a letra A apresenta na lista somente os dados que iniciam com essa letra. Digitando AR a lista apresenta somente quem se inicia com essas duas letras. E assim por diante.
A figura abaixo apresenta a nossa lista, que é pequena mas serve de exemplo.



Na Plan1 clique na célula B1 e insira a seguinte fórmula:

="Plan2!$A$" & CORRESP(ESQUERDA(Plan1!A1;NÚM.CARACT(A1));ESQUERDA(Plan2!A1:A1000;NÚM.CARACT(A1));0) & ":$A$" &CORRESP(ESQUERDA(Plan1!A1;NÚM.CARACT(A1));ESQUERDA(INDIRETO("Plan2!A1:A" & CONT.VALORES(Plan2!A1:A1000));NÚM.CARACT(A1)))

Como se trata de uma fórmula matricial, é preciso acrescentar os símbolos {}
Clique na Barra de fórmulas e tecle CTRL + SHIFT + ENTER

Clique em Inserir/Nome/Definir e na 1ª caixa digite o nome: TESTE
Na 2ª caixa digite a fórmula: =INDIRETO(Plan1!$B$1)

Selecione a célula A3 e clique em Dados/Validação.
Na 1ª caixa escolha Lista e na última caixa digite: =TESTE
Se houver alguma mensagem de erro, substitua a fórmula acima por:
=INDIRETO(TESTE)

Arraste até a célula desejada, no nosso caso a célula A25.
Resultado: qualquer caractere ou caracteres digitados na célula A1 filtra a lista nas células com validação de dados.

O ideal é que a fórmula digitada na célula B1 fique escondida em uma planilha à parte.



Conforme a figura acima, na célula A1 da Plan3 digite a seguinte fórmula:

="Plan2!$A$" & CORRESP(ESQUERDA(Plan1!A1;NÚM.CARACT(Plan1!A1));ESQUERDA(Plan2!A1:A1000;NÚM.CARACT(Plan1!A1));0) & ":$A$" &CORRESP(ESQUERDA(Plan1!A1;NÚM.CARACT(Plan1!A1));ESQUERDA(INDIRETO("Plan2!A1:A" & CONT.VALORES(Plan2!A1:A1000));NÚM.CARACT(Plan1!A1)))

Insira conforme explicado no início os símbolos {}

Clique em Inserir/Nome/Definir e na 1ª caixa digite o nome: FILTRO
Na 2ª caixa digite a fórmula: =INDIRETO(Plan3!$A$1)

Selecione na Plan1 a célula D3 e clique em Dados/Validação.
Na 1ª caixa escolha Lista e na última caixa digite: =FILTRO
Se houver alguma mensagem de erro, substitua a fórmula acima por:
=INDIRETO(FILTRO)

Arraste até a célula desejada, no nosso caso a célula A25.




Download do arquivo:

VALIDAÇÃO DEFININDO NOMES