Aparentemente, espaços em branco e strings vazias são duas coisas diferentes no Excel. Quando desejo usar um intervalo de células que contém strings vazias para um menu suspenso de validação de dados na célula e pedir para ignorar os espaços em branco, ele ainda mostra as strings vazias.
Você pode reproduzir isso entrando =""na célula A1 e depois entrando =ISBLANK(A1)em outra célula e ela retornará FALSE. Exclua a fórmula de A1 e ela retornará TRUE.
Seria bom se houvesse uma =BLANK()função.
Também tentei retornar =NA(), mas descobri que a lista suspensa também não ignora erros.
Responder1
Um menu suspenso de validação de dados não irá cooperar com você nisso. Ele deseja apenas olhar uma lista estática ou exatamente as células para as quais você o instruiu, sem pensar no que há nelas.
Como não podemos fazer com que ele faça o que queremos, vamos nos comprometer - daremos a ele um intervalo contínuo que mostre apenas os valores que queremos ver (mais os espaços em branco no final, onde as pessoas geralmente não olham).
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. Vou para outra coluna e digito 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 umfórmula de matriz, então pressionarei CTRL + SHIFT + ENTER quando terminar de digitar a fórmula. A seguir, copiarei/colarei ou preencherei 15 linhas. O resultado serão todos os meus valores não em branco de A1:A15 e, em seguida, todos os meus valores em branco no final.
Agora, abrirei a caixa de diálogo Validação de dados e apontarei 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, então ninguém precisa vê-los, a menos que rolem para baixo para não ver 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 contém seus valores e espaços em branco e permite informar qual célula deseja visualizar, por número.
SMALL(
olha para 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 além de uma string em branco
ROW($A$1:$A$15)
retorna o número da linha se isso acontecer,
,999),
e retorna 999 se isso não acontecer. 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 possui uma lista de números de linha, com 999 no lugar do número de linha real 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 esta fórmula. À medida que você copia a fórmula, o número aumenta. Isso informa ao pequeno que você deseja o primeiro menor número na primeira linha que contém a fórmula, o segundo menor na linha 2, etc.
O SMALLdevolve o número da linha para INDEX, o que fornece o valor dessa linha. Todos esses 999 se transformam em erros #REF no final, mas 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 um pouco com isso e você pegará o jeito.
Boa sorte!
Responder2
Algo assim funcionará?
=IF(AND(NOT(ISFORMULA(J3)),J3=""),"Truly Empty","Blank via Formula")
Nota: se você quiser uma função personalizada, como doingliteralmente =blank(A1), você precisará do VBA para fazer uma UDF. A fórmula acima verifica se a célula que você está procurando possui uma fórmula e também está em branco.
Responder3
Encontrei esse mesmo problema e encontrei uma maneira de resolver. Seguindo o exemplo compartilhado por AjimOthy, inclua uma fórmula de contagem abaixo de A1:A15 (digamos que A16 será esta célula). Ele contará apenas as células acima (A1:A15) e fornecerá um número lá
.
Eu então uso uma indireta como em
quando a validação de dados pede que você escolha sua fonte. Usar o indireto permite uma mudança fluida com base na contagem e também removerá os espaços em branco que aparecem na lista com a função de contagem, pois não conta os "" espaços em branco.