Aparentemente, los espacios en blanco y las cadenas vacías son dos cosas diferentes en Excel. Cuando quiero usar un rango de celdas que contiene cadenas vacías para un menú desplegable de validación de datos dentro de la celda y decirle que ignore los espacios en blanco, todavía muestra las cadenas vacías.
Puede reproducir esto ingresando =""en la celda A1 y luego ingresando =ISBLANK(A1)en otra celda y regresará FALSE. Elimina la fórmula de A1 y vuelve TRUE.
Sería bueno si hubiera una =BLANK()función.
También intenté regresar =NA(), pero resulta que la lista desplegable tampoco ignora los errores.
Respuesta1
Un menú desplegable de validación de datos no cooperará con usted en esto. Sólo quiere mirar una lista estática, o exactamente las celdas que usted le indica, sin pensar en lo que hay en ellas.
Como no podemos obligarlo a hacer lo que queremos, hagamos concesiones: le daremos un rango continuo que muestre solo los valores que queremos ver (más los espacios en blanco al final, donde la mayoría de la gente no mira).
En mi ejemplo, las celdas que contienen los valores (y espacios en blanco) para mi lista de validación de datos son A1:A15. Voy a ir a otra columna y escribiré la siguiente fórmula:
=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")
Necesito ingresar esto comofórmula matricial, así que presionaré CTRL + MAYÚS + ENTRAR cuando termine de escribir la fórmula. A continuación, copiaré/pegaré o completaré 15 filas. El resultado serán todos mis valores que no estén en blanco de A1:A15, luego todos mis valores en blanco al final.
Ahora, abriré el cuadro de diálogo Validación de datos y señalaré mi lista a las celdas que contienen mis fórmulas (no A1:A15). Los espacios en blanco todavía están allí, pero al final, por lo que nadie necesita verlos a menos que se desplacen hacia abajo para no ver nada.
No es del todo limpio, pero sí dinámico y evita las macros.
Entonces, ¿qué hizo esa fórmula?
=INDEX($A$1:$A$15,
mira el rango original que tiene sus valores y espacios en blanco, y le permite decirle qué celda desea mirar, por número.
SMALL(
mira un conjunto de números, cuenta desde el más pequeño y devuelve uno,
IF($A$1:$A$15<>"",
comprueba si cada celda de su rango coincide con algo que no sea una cadena en blanco
ROW($A$1:$A$15)
devuelve el número de fila si lo hace,
,999),
y devuelve 999 si no es así. Si tuviera más de 1000 filas, necesitaría un número mayor aquí, pero (con suerte) esto es más que suficiente para sus opciones de validación de datos. La función PEQUEÑA ahora tiene una lista de números de fila, con 999 en lugar del número de fila real para los espacios en blanco.
ROW(1:1)
es una forma dinámica de comenzar a contar desde cualquier celda en la que ingrese esta fórmula. A medida que copia la fórmula, el número crece. Esto le indica al pequeño que desea el primer número más pequeño en la primera fila que contiene la fórmula, el segundo más pequeño en la fila 2, etc.
Le SMALLdevuelve el número de fila a INDEX, lo que le da el valor de esa fila. Todos esos 999 se convierten en errores #REF al final, pero silenciosamente los convertimos nuevamente en espacios en blanco con el archivo IFERROR.
Esa fórmula puede ser difícil de entender. Si tiene problemas, intente configurar datos de muestra en A1:A15 en una hoja nueva, seleccione una celda en otra columna y copie mi fórmula exactamente en la barra de fórmulas (no olvide CTRL+MAYÚS+ENTRAR). Juega un poco con eso y deberías dominarlo.
¡Buena suerte!
Respuesta2
¿Funcionará algo como esto?
=IF(AND(NOT(ISFORMULA(J3)),J3=""),"Truly Empty","Blank via Formula")
Nota: si desea una función personalizada, como hacer literalmente =blank(A1), necesitará VBA para crear una UDF. La fórmula anterior verifica si la celda que estás buscando tiene una fórmula y también está en blanco.
Respuesta3
Me encontré con el mismo problema y encontré una manera de resolverlo. Siguiendo el ejemplo compartido por AjimOthy, incluya una fórmula de recuento debajo de A1:A15 (digamos que A16 será esta celda). Solo contará las celdas anteriores (A1:A15) y le dará un número allí
.
Luego uso una indirecta como en
cuando la validación de datos le pide que elija su fuente. El uso del indirecto permite un cambio de fluido basado en el conteo y también eliminará los espacios en blanco que aparecen en la lista con la función de conteo ya que no cuenta los "" espacios en blanco.