Um menu suspenso de validação de dados não vai cooperar com você sobre isso. Ele só quer olhar para uma lista estática, ou exatamente para as células que você diz, sem pensar no que está nelas.
Como não podemos fazer o que queremos, vamos nos comprometer - vamos dar um intervalo contínuo que mostre apenas os valores que queremos ver (mais os espaços em branco no final, onde as pessoas não vão parecer ).
No meu exemplo, as células que contêm os valores (e espaços em branco) da minha lista de validação de dados são A1: A15. Eu vou para outra coluna e digite a seguinte fórmula:
=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")
Eu preciso inserir isso como uma fórmula de matriz , então pressionarei CTRL + SHIFT + ENTER quando terminar de digitar a fórmula. Em seguida, vou copiar / colar ou preencher 15 linhas. O resultado será todos os meus valores não em branco de A1: A15, então todos os meus espaços em branco no final.
Agora, abro a caixa de diálogo Validação de dados e aponto minha lista para as células que contêm minhas fórmulas (não A1: A15). Os espaços em branco ainda estão lá, mas no final, ninguém precisa vê-los, a menos que eles rolem para lá e não olhem para o nada.
Não é totalmente limpo, mas é dinâmico e evita macros.
Então, o que essa fórmula fez?
=INDEX($A$1:$A$15,
analisa o intervalo original que tem seus valores e espaços em branco, e permite dizer a qual célula você deseja analisar, por número.
SMALL(
analisa um conjunto de números, conta a partir do menor e devolve um,
IF($A$1:$A$15<>"",
verifica se cada célula do seu intervalo corresponde a algo que não seja uma string em branco
ROW($A$1:$A$15)
retorna o número da linha, se o fizer,
,999),
e retorna 999, se não. Se você tivesse mais de 1.000 linhas, precisaria de um número maior aqui, mas isso é (espero) mais que suficiente para suas opções de validação de dados. A função SMALL agora tem uma lista de números de linhas, com 999 no lugar do número real da linha para quaisquer espaços em branco.
ROW(1:1)
é uma maneira dinâmica de começar a contar a partir de qualquer célula em que você inserir essa fórmula. À medida que você copia a fórmula, o número aumenta. Isto diz ao pequeno que você quer o 1º menor número na 1ª linha contendo a fórmula, o 2º menor na linha 2, etc.
O SMALL retorna o número da linha para INDEX , que fornece o valor para essa linha. Todos esses 999s se transformam em erros #REF no final, mas nós silenciosamente os transformamos em espaços em branco com o IFERROR .
Essa fórmula pode ser difícil de entender. Se estiver com problemas, tente configurar dados de amostra em A1: A15 em uma nova planilha, escolhendo uma célula em outra coluna e copiando minha fórmula exatamente na barra de fórmulas (não se esqueça de CTRL + SHIFT + ENTER). Brinque com isso um pouco, e você deve pegar o jeito dele.
Boa sorte!