[Validação de Dados] LISTA: Validação por lista misto (em cascata + campo livre)

Olá amigos, seguidores e amantes do Excel! Depois de um longo tempo ausente aqui no blog, estou me organizando para voltar a compartilhar dicas aqui.

Esta semana recebi a seguinte dúvida de uma usuária:

“Estou fazendo um diário de bordo e em uma coluna, vou colocar uma validação de dados (lista) com “Rotina” e “Demanda Extra”. A partir daí, quero que a próxima coluna tenha uma validação de dados (Lista) específica se a opção anterior for “Rotina” e se a opção anterior for “Demanda Extra”, quero que esta coluna fique livre para a pessoa digitar a atividade.”

Vamos então a uma proposta de solução para esta questão.

No problema acima, só é necessário uma lista em cascata para a opção “Rotina”. Vamos dificultar um pouco mais o problema para termos uma solução que demonstre também como fazer facilmente uma validação em lista em cascata para N valores. Imagine que além destes dois campos do problema (“Rotina” e “Demanda Livre”) exitam mais campos que exijam a validação em cascata, que representaremos na tabela abaixo:

Validação Cascata 1

Tendo esta tabela inserida em algum lugar da planilha, e devidamente configurada como tabela (Guia “INSERIR”, botão “Tabela”), o primeiro passo da minha proposta de solução é definir NOMES para cada coluna da tabela, com exceção da coluna de DEMANDA LIVRE ao qual será um campo aberto.

BOA PRÁTICA:

Antes de nomear os intervalos da tabela, uma boa prática é nomear a sua tabela. Se você definiu corretamente o intervalo todo como “Tabela”, ao clicar em qualquer célula dentro dela, aparecerá a guia contextual “Ferramenta de Tabela” na faixa de opções, onde você poderá renomear a sua tabela. Neste exemplo, renomeie de “tabela1” para “tListaSuspensa”.

Validação Cascata 2

Para definir os nomes, selecione os valores de uma coluna, vá na guia “FÓRMULAS” e na opção “Definir Nome”, e aparecerá uma caixa de diálogo para definir o nome do intervalo. Note que a referência do endereço do nome está com a nomenclatura de endereço de tabelas e não no formato de letra e número. Isso é bom e desejável, pois um dos benefícios de se trabalhar com tabelas é que caso você adicione novos itens na coluna, não precisará se preocupar em alterar as fórmulas que se referem a coluna, pois a referência “tListaSuspensa[ROTINA]” sempre se referirá a toda a coluna ROTINA.

Validação Cascata 3

Repita a operação para as outras duas colunas, sempre deixando o nome definido com o mesmo nome da coluna. Deixei propositalmente um nome composto para as duas colunas do meio. Caso tenha tentado nomear o intervalo atribuindo o nome composto, você notará que o Excel não permitirá, pois ele não aceita nome composto. Dessa forma defina com o nome sem espaço, ficando “AtividadesOperacionais” e AtividadesAdministrativas”. Veja a janela do gerenciador com todos os nomes criados:

Validação Cascata 4

Após nomear o intervalo, vamos criar a tabela para lançamento do diário de bordo com essas atividades e o tipo de atividade e então criar a validação em cascata.

Criada a tabela, selecionamos o intervalo da primeira coluna e aplicamos uma validação de dados simples com lista suspensa (Guia “DADOS, opção “Validação de Dados”) onde aproveitaremos os cabeçalhos da tabela tListaSuspença como fonte para a lista:

Validação Cascata 5

Próximo passo é criar a validação de dados na coluna “Detalhe da atividade”, que deve ser uma validação dependente do que foi escolhido na coluna Atividade de acordo com os valores que estão na tabela, e caso seja selecionado a opção DEMANDA LIVRE a célula fique livre para o usuário digitar qualquer informação. Para isso, vamos validar este intervalo por lista, mas como fonte usaremos a seguinte fórmula com o intervalo todo selecionado da coluna DETALHE ATIVIDADE:

=se(B4="DEMANDA LIVRE";C4;INDIRETO(SUBSTITUIR(B4;" ";"")))

Validação Cascata 6

Surgirá a mensagem “A Fonte Atualmente resulta em erro. Deseja continuar?”. Clique em SIM!

Pronto a validação desejada está feita!

Vamos entender a fórmula de validação:

=SE(B4="DEMANDA LIVRE";C4;INDIRETO(SUBSTITUIR(B4;" ";"")))

A função SUBSTITUIR usamos para remover os espaços do nome da coluna atividade, pois nomeamos os intervalos com nomes sem espaçamento devido regra do Excel.

A função INDIRETO se encarregará de converter o texto gerado pela função SUBSTITUIR em um argumento reconhecível pelo Excel, no caso o nome do intervalo que nomeamos.

A função SE é a condicional em que caso o valor selecionado na lista seja diferente de DEMANDA LIVRE, traga a lista correspondente de valores da tabela, e caso seja DEMANDA LIVRE a fonte da lista será a própria célula, que neste caso não cria uma referência circular e podemos concluir que o Excel processa a validação de dados após o dado ser inserido na célula.

Validação Cascata 7

Link para baixar este exemplo: Validação com lista e campo livre condicional

Se tiverem outras dúvidas me escrevam no email maniadeexcel@gmail.com. Quem sabe não transformo também sua dúvida num artigo aqui no blog?

Abraços a todos!!

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s