Validação de Dados Tripla Dinâmica no Excel

Neste artigo é demonstrado como fazer uma validação de dados tripla e dinâmica no Excel, fazendo assim uma validação dados com lista em cascata.

Olá pessoal, vamos mais um artigo interessante onde vou abordar a Validação de Dados Dinâmica e Tripla, e se possível compartilhar e curtir este artigo agradecerei muito!

Vamos ao desafio:

A "Validação de Dados" dentro do Excel é uma ferramenta muito interessante, mais ainda quando conseguimos juntar conceitos das formulas dinâmicas

Criando a validação de dados em cascata

Para fazer a primeira Validação de Dados das "Universidades" é necessário criar uma coluna auxiliar para que traga somente os valores digitados de maneira elegante, do primeiro registro para o último registro, caso tenha algum registro inserido no meio ele irá inserir e manter a elegância da apresentação.

Segue a Formula na Célula R2:

=SEERRO(ÍNDICE($A$1:$N$1;0;MENOR(ÍNDICE((($A$1:$N$1<>"") * COL($A$1:$N$1))*1;0);CONTAR.VAZIO($A$1:$N$1)+LINS($R$2:R2)));"")

Ou a formula em inglês

=IFERROR(INDEX($A$1:$N$1;0;SMALL(INDEX((($A$1:$N$1<>"") * COLUMN($A$1:$N$1))*1;0); COUNTBLANK($A$1:$N$1)+ROWS($R$2:R2)));"")

Arrastei neste exemplo até a célula R20, para caso inserisse novo valor ele já insere conforme a imagem acima.

Agora, é possível vincular esses resultados na primeira validação de dados que será na célula B2

Lembrando que para colocar uma fórmula na Validação de Dados é só ir no Menu Dados->Validação de Dados->Validação de Dados->Configurações-> Permitir->Lista e depois em Fonte: você deverá colocar a formula nesse espaço em branco.

Segue a Fórmula:

=DESLOC($R$2;0;0;CONT.SE($R:$R;">a");1) Ou em inglês =OFFSET($R$2;0;0;COUNTIF($R:$R;">a");1)

Utilizei o CONT.SE(R:R;">a") pois como CONT.VALORES ele retorna os valores com formulas e o CONT.SE(R:R;">a") ele só irá retornar 2.

Como esse é um recurso também muito interessante, falarei dele em um outro artigo. Vamos continuar,

Para a Segunda Validação de Dados na célula B3 agora que começa as aplicações interessantes dentro do Excel

Segue a Fórmula:

=DESLOC($A$1;1;CORRESP($B$2;$1:$1;0)-1;1; CONT.SE(DESLOC($A$1;1;CORRESP($B$2;$1:$1;0)-1;1;6) ; ">a"))

ou em inglês =OFFSET($A$1;1;MATCH($B$2;$1:$1;0)-1;1; COUNTIF(OFFSET($A$1;1;MATCH($B$2;$1:$1;0)-1;1;6); ">a"))

A estratégia desta fórmula é a busca dinâmica dos intervalos da Universidade 1 e 2 e contar em um intervalo de 6 colunas (o que pode ser ajustado), resultando somente o intervalo preenchido 3 Colunas - Universidade 1 e 4 Colunas - Universidade 2

E por último a 3° Validação de Dados que está na célula B4

Segue a Fórmula:

=DESLOC($A$1;2;CORRESP(B2;1:1;0)+ CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0)-2; CONT.VALORES(DESLOC($A$1;2;CORRESP(B2;1:1;0)+ CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0) -2;100;1));1) 

ou em inglês =OFFSET($A$1;2;MATCH(B2;1:1;0)+MATCH(B3;OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0)-2; COUNTA(OFFSET($A$1;2;MATCH(B2;1:1;0)+MATCH(B3; OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0)-2;100;1));1)

Neste deixei também um intervalo de 200 linhas o que é possível alterar caso tenha mais registros.

E para terminar e alertar o usuário se está correto ou não é só fazer uma condição de busca de 1 ou 0 com formatação condicional, segue a formula na célula C3:

=SE(ÉERROS(CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0));0;1)

ou em inglês =IF(ISERROR(MATCH(B3;OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0));0;1)

E na Célula C4:

=SE(ÉERROS(CORRESP(B4;DESLOC($A$1;2;CORRESP(B2;1:1;0) +CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0)-2; CONT.VALORES(DESLOC($A$1;2;CORRESP(B2;1:1;0)+ CORRESP(B3;DESLOC($A$1;1;CORRESP(B2;1:1;0)-1;1;6);0)-2;100;1));1);0));0;1)

ou em inglês
=IF(ISERROR(MATCH(B4;OFFSET($A$1;2;MATCH(B2;1:1;0)+ MATCH(B3;OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0)-2; COUNTA(OFFSET($A$1;2;MATCH(B2;1:1;0)+MATCH(B3; OFFSET($A$1;1;MATCH(B2;1:1;0)-1;1;6);0)-2;100;1));1);0));0;1)

E então? Gostou do artigo? Se gostou, compartilhe com seus contatos e vamos juntos explorar essa ferramenta maravilhosa que é o Excel!

Você tem uma outra maneira de fazer a Validação de Dados Tripla? Escreva para mim aqui embaixo nos comentários!

OBS: Obrigado meu grande amigo Alessandro Trovato pelas dicas e revisões no texto

Abraços

Baldini Fabio

Veja mais artigos em guiadoexcel.com.br

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: