Filtro ao Digitar Excel - Validação de Dados Lista

Como fazer um filtro ao digitar no Excel utilizando validação dados com lista e a função Filtro.

Esta é uma melhoria do artigo: https://www.guiadoexcel.com.br/pesquisar-texto-ao-digitar-no-excel-em-lista-de-validacao/ aonde mostramos como fazer uma lista pesquisável ao digitar.

No entanto ela era limitada a uma célula, reduzindo assim muito a sua aplicação.

Pensando nisso fiz algumas adaptações e com este ajuste conseguimos retornar para qualquer item de uma lista de validação de dados facilmente.

Estrutura da planilha

A estrutura da planilha é igual a planilha anterior ou muito parecida com qualquer outra que você tenha.

Temos uma base de dados:

E um local aonde queremos retornar itens desta base de dados conforme é realizada uma seleção.

Na lista abaixo o objetivo é que na coluna Local, ao digitar uma parte do texto e dar um Enter ou Ctrl+Enter na célula a mesma ter uma lista com a pesquisa de somente os itens que tem aquele texto digitado.

O objetivo é que ao digitar ele apareça como abaixo, mas em uma lista.

Função Cél para Preencher a Lista de Validação

Para preencher a lista de validação com a pesquisa automática, independente da quantidade de itens utilizamos a função CÉL.

A função Cél retorna informações da célula que acabou de ser alterada.

Podemos retornar diversas informações da célula, inclusive o seu conteúdo.

Pensando nisso usamos a função CÉL da seguinte forma: =CÉL("conteúdo").

O resultado disso é que ao alterar qualquer célula da planilha o Excel irá retornar nesta fórmula o conteúdo.

No exemplo que temos, digitamos por exemplo ME, em uma célula e o retorno nesta função CÉL será ME automaticamente.

Fórmula com a Pesquisa Digitada

Para aplicar um filtro e retornar somente os dados que contém o texto digitado usamos a função FILTRO.

A função Filtro permite que sejam realizadas consultas dinâmicas e de uma forma muito prática e facilmente adaptável.

No nosso exemplo usamos a seguinte fórmula:

=CLASSIFICAR(FILTRO($B$10:$B$27;(ÉNÚM(LOCALIZAR(L9;$B$10:$B$27;1)));"SEM DADOS"))

nela utilizamos a função FILTRO para retornar apenas se forem retornados números (posições) nos textos que contenham o conjunto de letras.

Depois disso é feito a classificação com a função Classificar e os dados são retornados para uma lista conforme abaixo:

O Excel retorna uma matriz dinâmica de dados com o resultado de todas as palavras que contém o texto e de forma classificada.

Aplicando o Filtro na Validação de Dados do Excel

Agora na validação de dados você deve selecionar os itens que terão a consulta pesquisável.

Clique em Dados->Validação de dados e desmarque a opção em Alerta de Erro chamada Mostrar alerta de erro após a inserção de dados inválidos.

Esta opção deve estar desmarcada para que possa digitar um texto e não acusar um erro.

Em Configurações, nesta mesma tela, altere Permitir para Lista e altere a Fonte para a primeira célula da fórmula FITLRO que fizemos antes. Aonde tem o retorno dos dados da pesquisa no Excel.

IMPORTANTE: Digite # depois da célula para que este intervalo seja identificado como um intervalo dinâmico, pois caso contrário não irá funcionar.

Resultado

O resultado da planilha é que ao digitar um valor em uma célula da lista da pesquisa o Excel altera o conteúdo da célula com a função CÉL e por conseguinte altera também o resultado da função FILTRO e por fim altera a lista de validação de dados.

No topo deste artigo há também um vídeo mostrando passo-a-passo como implementar um filtro ao digitar na lista de validação de dados do Excel.

Download da Planilha de Filtro ao Digitar

Realize o download do arquivo deste exemplo e da vídeo-aula acima neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: