Fórmula genérica
=COUNT(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),allowed&"",0))=LEN(A1)
Resumo
Para usar a validação de dados para permitir uma lista de caracteres específicos apenas, você pode usar uma fórmula de matriz bastante complicada com base nas funções COUNT, MATCH e LEN. No exemplo mostrado, a validação de dados é aplicada com esta fórmula:
=COUNT(MATCH(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),allowed&"",0))=LEN(B5)
onde "permitido" é o intervalo nomeado D5: D11.
Explicação
Trabalhando de dentro para fora, a função MID é usada para gerar uma matriz de texto inserido em B5 com este trecho:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
explicado em detalhes aqui. O resultado é uma matriz como esta:
("A";"A";"A";"-";"1";"1";"1")
que vai para MATCH como o valor de pesquisa. Para a matriz de pesquisa, usamos o intervalo nomeado "permitido", concatenado a uma string vazia (""):
allowed&""
A concatenação converte quaisquer números em strings, de modo que estamos combinando maçãs com maçãs. O resultado é uma matriz como esta:
("A";"B";"C";"1";"2";"3";"-")
O último argumento em MATCH, match_type é definido como zero para forçar uma correspondência exata. Como fornecemos a MATCH vários valores de pesquisa, obtemos uma matriz com vários resultados:
(1;1;1;7;4;4;4)
Cada número nesta matriz representa uma correspondência. Caso uma correspondência não seja encontrada para um caractere, a matriz conterá um erro # N / A.
Finalmente, a função COUNT é usada para contar os números na matriz de resultado, que é comparada a uma contagem de todos os caracteres na célula calculada com a função LEN. Quando MATCH encontra uma correspondência para todos os caracteres, as contagens são iguais, a fórmula retorna TRUE e a validação de dados é bem-sucedida. Se MATCH não encontrar nenhum caractere correspondente, ele retornará # N / A em vez de um número. Nesse caso, as contagens não correspondem e a validação de dados falha.
Nota: esta fórmula depende da força bruta para fazer o trabalho. Se você tiver uma abordagem melhor, deixe um comentário abaixo.
