Listas desplegables dependientes en Excel paso a paso

Halloway Plant Hire facturó 6.933,00 € por los partes de trabajo de octubre. El mes valía 10.982,00 €. Todas las máquinas salieron, todas las tarifas de la ficha eran correctas y tres de los alquileres se facturaron a cero al día.

Cada una de esas filas se rellenó desde una lista desplegable, y la lista las aprobó todas. Las aprobó en septiembre, cuando la celda del depósito de al lado decía todavía otra cosa.

Una regla de validación se ejecuta una sola vez, en el momento en que se escribe el valor. Si después cambias la celda de la que depende, no se vuelve a ejecutar nada. El hijo conserva un valor que su padre ya no admite, y lo conserva en una celda que sigue mostrando su flecha.

Una lista dependiente son dos cosas: una lista que se acota y una pareja que tiene que seguir siendo cierta. Excel te da la primera. Para la segunda no te da absolutamente nada.

Qué necesitas. INDIRECTO, SUSTITUIR, CONTAR.SI, CONTAR.SI.CONJUNTO, SUMAR.SI.CONJUNTO, CONTARA, ESPACIOS y LARGO funcionan en todas las versiones de este siglo, igual que los nombres definidos, la validación de datos y Rodear con un círculo datos no válidos.

UNICOS, FILTRAR, ORDENAR, LET, BUSCARX y las referencias de desbordamiento como =$K$2# necesitan Microsoft 365 o Excel 2021 y posteriores. La sección 7 dice qué hacer si tu versión las rechaza.

El alquiler de maquinaria es solo el ejemplo. Un proveedor y sus referencias, un cliente y sus expedientes, una obra y sus centros de coste, un país y sus tipos de IVA: las mismas dos celdas y el mismo silencio.

01Doce Partes de Trabajo, Tres a Cero

La columna B es el depósito y la C la máquina. La lista de máquinas se acota con el depósito, porque una Telehandler en North East cuesta 148,00 € al día y la misma máquina en South West cuesta 142,00 €.

La columna D es la tarifa que calculó la hoja, la E son los días y la F lo que fue a la factura. Ocho filas están bien. Cuatro no, y están mal de dos maneras distintas.

Tres de ellas no facturaron nada, porque el depósito y la máquina nombran una pareja que la ficha de tarifas no tiene. La cuarta facturó una excavadora de 8 toneladas a 175,00 € en lugar de una de 13 toneladas a 265,00 €.

02La Regla Corre una Vez, al Escribir el Valor

La validación de datos es una comprobación de entrada. Excel evalúa la regla cuando alguien escribe o elige un valor en esa celda, y guarda la regla, no el veredicto. Después no la vuelve a ejecutar nadie, y nada vigila las celdas de las que depende.

Así que una lista dependiente solo es cierta en el instante en que se usa. JS-4019 se emitió con South West y una Telehandler, que ese día era una pareja legal. En septiembre el trabajo pasó a Midlands, alguien cambió la celda del depósito y la celda de la máquina se quedó intacta.

Excel no tiene ningún evento que borre al hijo cuando cambia su padre, y una celda con un valor escrito no tiene fórmula que recalcular. La fila sigue mostrando la flecha, la flecha ofrece ahora las cuatro máquinas de Midlands, y la celda sigue diciendo Telehandler.

03La Lista Que Se Acota: INDIRECTO y Nombres

La construcción clásica son dos reglas y un truco. La celda padre lleva una regla de Lista con los depósitos. La celda hija lleva una regla de Lista cuyo Origen es =INDIRECTO($B2), e INDIRECTO convierte el texto de B2 en el rango con nombre que se llama igual.

Eso significa un nombre por depósito. Coloca la ficha de tarifas con los nombres de los depósitos como encabezados, selecciona el bloque y usa Fórmulas → Crear desde la selección → Fila superior: Excel escribe un nombre por columna de una sola vez.

Hoja Tarifas         Nombres creados
South_West  Midlands  North_East        South_West = $A$2:$A$5
3T Excavator  Dumper 6T   3T Excavator   Midlands   = $B$2:$B$5
8T Excavator  8T Excavator Telehandler   North_East = $C$2:$C$5
Telehandler   13T Excavator Tandem Roller

El cuadro Origen no es el sitio para probar la idea. Escribe antes =INDIRECTO($B2) en una celda vacía: si devuelve #¡REF!, el nombre no existe, y la lista estará vacía exactamente por lo mismo.

04$C$2 Da a Todas las Filas la Lista de la Primera

Las reglas de validación se escriben para la celda activa y se copian al resto del rango igual que una fórmula, así que los dólares significan lo de siempre. $B2 fija la columna y deja viajar a la fila. $B$2 fija las dos.

Escribe =INDIRECTO($B$2) y todas las filas de la columna le preguntan a la fila 2 qué lista les toca. La fila 2 decía South West, así que la fila 27 ofrece las máquinas de South West diga lo que diga su propia celda de depósito.

Eso es JS-4026. La celda del depósito dice North East, la lista ofreció 8T Excavator porque arriba de la columna había un trabajo de South West, y North East no alquila ninguna de 8 toneladas.

Selecciona toda la columna antes de abrir el cuadro de diálogo y escribe la regla para su celda superior. Excel la desplaza hacia abajo por la selección, igual que haría con una fórmula.

05Un Nombre No Admite Espacios

Los nombres definidos admiten letras, números, guiones bajos y puntos, y nada más. South West es un depósito, pero South West no puede ser un nombre, así que Crear desde la selección escribe South_West sin decir nada y =INDIRECTO($B2) pide un nombre que no existe.

El arreglo es una función más en el cuadro Origen:

=INDIRECTO(SUSTITUIR($B2;" ";"_"))

Lo mismo pasa con un nombre que empieza por una cifra, con uno que choca con una dirección de celda como Q1 y con cualquier signo de puntuación. Las comas y los ampersands de los nombres de proveedor son los sospechosos habituales, y fallan con el mismo silencio: lista vacía y ningún error.

Una lista vacía es un nombre que falta nueve de cada diez veces. La flecha aparece igual, la lista no tiene nada dentro, y quien va con prisa escribe el valor a mano, que es como acaba en la celda una máquina que el depósito no alquila.

06La Lista Que Se Quedó Corta

Un rango con nombre es una dirección fija. Midlands = Tarifas!$B$2:$B$5 son cuatro celdas, y son cuatro celdas para siempre, por muchas máquinas que alguien añada debajo.

Halloway añadió una excavadora de 13 toneladas a la columna de Midlands en agosto, en la fila 6. El nombre seguía acabando en la fila 5. La lista ofrecía tres máquinas, la 13T no estaba entre ellas y el mostrador eligió la de 8T a 175,00 € para una máquina que se alquila a 265,00 €.

No falló nada. La pareja que eligió el mostrador existe en la ficha de tarifas, así que la tarifa la encontró y la fila facturó 1.400,00 € frente a 2.120,00 € debidos. Las comprobaciones de la sección 12 no cazan esta fila, y por eso la lista tiene que poder crecer.

Da formato de tabla a la ficha con Ctrl+T y el nombre de la columna crece con los datos. Una lista hecha con UNICOS y FILTRAR, en la sección siguiente, también crece, y no necesita ningún nombre.

07UNICOS, FILTRAR y una Referencia de Desbordamiento

Una ficha de tarifas ordenada no necesita una columna por depósito. Déjala en tres columnas normales — depósito, máquina y tarifa — y que la lista salga de ahí:

=ORDENAR(UNICOS(FILTRAR(Tarifas!$C$2:$C$40;Tarifas!$B$2:$B$40=$B2)))

En Microsoft 365 esa fórmula va directa al cuadro Origen con $B2 relativo, y cada fila de la columna tiene su propia lista, ordenada y sin repetidos. Una regla, ningún nombre, nada que ampliar.

Si tu versión rechaza una fórmula ahí, desborda la lista en una celda auxiliar —pongamos K2— y apunta el Origen a =$K$2#. Las versiones que tampoco admiten el # sí admiten un nombre definido cuyo Se refiere a sea =Hoja1!$K$2#.

La ficha de tarifas pasa a ser un solo bloque que mantener, y el mismo bloque alimenta la búsqueda de la columna D. Una lista y una búsqueda hechas sobre rangos distintos acabarán discrepando, y esa discrepancia es lo que factura un parte de trabajo.

08El Pegado Atraviesa la Validación

Pegar no comprueba el valor contra la regla del destino. Sustituye la regla por la de la celda de origen, o por ninguna, y copia el valor igual. Un solo Ctrl+V puede dejar un bloque entero sin validación y sin flechas.

JS-4031 llegó así, desde el correo del mostrador: 8T Excavator , con un espacio final. No es una entrada legal en ninguna parte, en pantalla es idéntica a la fila de arriba, y SUMAR.SI.CONJUNTO no coincidió con nada: la fila facturó 0,00 € por cinco días.

=LARGO(C9)                   → 13
=LARGO(ESPACIOS(C9))         → 12
=IGUAL(C9;"8T Excavator")    → FALSO

Pegado especial → Valores pega el valor sin tocar la regla, pero tampoco la comprueba, así que la pareja caducada entra igual. Nada en Excel valida al pegar.

Lo que sí lo encuentra después es Datos → Validación de datos → Rodear con un círculo datos no válidos, que marca todas las celdas cuyo valor actual incumple su propia regla. Es el clic que lee la hoja entera, y habría rodeado JS-4019, JS-4026 y JS-4031 a la vez.

09Valida la Pareja, No las Dos Celdas

Una celda admite una regla de validación. Una regla de Lista llena el desplegable; una regla Personalizada puede comprobar lo que quieras; las dos en la misma celda, no. Así que la comprobación de la pareja tiene que vivir en otro sitio, y una columna propia es el más barato.

=CONTAR.SI.CONJUNTO(Tarifas!$B:$B;$B2;Tarifas!$C:$C;$C2)=1

VERDADERO significa que la ficha de tarifas tiene exactamente una fila para ese depósito y esa máquina. FALSO significa que la fila no es facturable, ya sea porque cambió el depósito, porque el pegado vino sucio o porque alguien la escribió a mano.

LET deja la misma comprobación legible cuando además carga con la consecuencia:

=LET(parejas;CONTAR.SI.CONJUNTO(Tarifas!$B:$B;$B2;Tarifas!$C:$C;$C2);
     SI(parejas=1;"OK";SI(parejas=0;"No existe la pareja";"Duplicada en la tarifa")))

Un recuento mayor que 1 también conviene cazarlo. Dos filas en la ficha para un mismo depósito y máquina significan que SUMAR.SI.CONJUNTO suma las dos y la fila factura el doble, que es la única versión de este fallo que cobra de más.

10Borrar al Hijo Cuando Cambia el Padre

La respuesta honesta es que una celda escrita no puede borrarse sola. Una celda de lista guarda un valor, no una fórmula, así que no hay nada que recalcular cuando cambia el depósito de al lado. Excel no lo va a hacer por ti.

Lo que sí puedes hacer es que la caducidad se vea en el momento en que ocurre. La columna de pareja de la sección 9 lo hace en una celda, y un formato condicional sobre ella lo hace en color, en toda la fila, sin que nadie tenga que ejecutar nada.

Si el hijo se puede deducir, no debería ser una lista desplegable. Donde un depósito solo alquila una clase de máquina, =BUSCARX($B2;Tarifas!$B:$B;Tarifas!$C:$C;"") es una celda que nadie puede dejar caducada, y no hay ninguna regla que mantener al día.

11Por Qué la Tarifa Fue Cero y No un Error

SUMAR.SI.CONJUNTO responde cuánto, sobre las filas que coinciden. Cuando no coincide ninguna, la respuesta honesta es cero, y el cero es un número: se formatea como dinero, suma como dinero y se imprime en una factura como dinero.

Una búsqueda hace otra pregunta. BUSCARX e INDICE/COINCIDIR devuelven #N/A cuando la clave no está, y #N/A es ruidoso: se extiende a todos los totales de arriba y alguien tiene que resolverlo antes de que salga el archivo.

=SUMAR.SI.CONJUNTO(Tarifas!D:D;Tarifas!B:B;$B2;Tarifas!C:C;$C2)   → 0,00
=BUSCARX($B2&"|"&$C2;Tarifas!$F:$F;Tarifas!$D:$D)                 → #N/A

La segunda construye una clave con la pareja usando & y la busca en una columna de claves de la ficha. Prefiere la fórmula que falla a gritos en una hoja que factura a alguien, y deja SI.ERROR y SI.ND fuera de ella hasta haber entendido los ceros.

Donde SUMAR.SI.CONJUNTO tenga que quedarse —y suele tener que quedarse, porque suma y una búsqueda no—, protégelo con el recuento:

=SI(CONTAR.SI.CONJUNTO(Tarifas!$B:$B;$B2;Tarifas!$C:$C;$C2)=0;NOD();
    SUMAR.SI.CONJUNTO(Tarifas!D:D;Tarifas!B:B;$B2;Tarifas!C:C;$C2))

12Las Comprobaciones Que lo Cazan Antes de Facturar

Cinco celdas encima de la tabla habrían cazado octubre, y cada una es una pregunta cuya respuesta ya conoces.

1. ¿Nombra cada fila una pareja que existe? =SUMAPRODUCTO(--(CONTAR.SI.CONJUNTO(Tarifas!$B:$B;$B2:$B13;Tarifas!$C:$C;$C2:$C13)=0)) tiene que dar cero. Es JS-4019, JS-4026 y JS-4031 enteras en una celda.
2. ¿Hay alguna tarifa a cero? =CONTAR.SI(D2:D13;0) tiene que dar cero. Un alquiler a nada al día nunca es un alquiler: es una pareja que no coincidió.
3. ¿Alguna máquina lleva espacios? =SUMAPRODUCTO(--(LARGO(C2:C13)<>LARGO(ESPACIOS(C2:C13)))) tiene que dar cero, y es la comprobación que encuentra una fila pegada.
4. ¿Hay alguna máquina que no esté en la ficha? =SUMAPRODUCTO(--(CONTAR.SI(Tarifas!$C:$C;C2:C13)=0)) tiene que dar cero. Separa una errata de una máquina en el depósito equivocado.
5. ¿Cuadra la factura con las tarifas? =SUMA(F2:F13)-SUMAPRODUCTO(D2:D13;E2:E13) tiene que dar cero, o alguien ha sobrescrito una tarifa en una fila.

Ejecuta Rodear con un círculo datos no válidos antes de exportar, no después de la reclamación. Cuesta un clic, lee todas las celdas validadas de la hoja y los círculos desaparecen en cuanto se corrige cada valor.

13Diez Cosas Que Muerden

1. La validación solo corre al escribir. Cambia el padre y el hijo conserva un valor que su propia lista ya no ofrece.
2. $C$2 ancla la lista a la fila de arriba. Escribe la regla para la primera celda de la selección dejando la fila relativa.
3. Un nombre no admite espacios. South West pasa a ser South_West, e INDIRECTO necesita SUSTITUIR para encontrarlo.
4. Un rango con nombre no crece. La máquina añadida en agosto nunca llega a la lista escrita en julio.
5. El pegado se salta la validación entera. Sustituye la regla además del valor, y no avisa de nada.
6. Una regla de Lista no deja sitio para la pareja. Una celda, una regla: la comprobación va en una columna.
7. SUMAR.SI.CONJUNTO devuelve cero cuando no coincide nada. En una factura, el cero es un número y nadie lo reclama.
8. Un aviso de Advertencia no es uno de Detener. Deja pasar el valor con un clic, y la fila queda idéntica después.
9. Dos filas de tarifa para una pareja facturan el doble. Comprueba =1, no >0.
10. Una lista vacía se rodea escribiendo. Un nombre que falta enseña la flecha y ninguna opción, y lo siguiente es texto libre.

14Mini Ejercicios

1. Rómpela a propósito. Monta las dos listas sobre la hoja de ejemplo, rellena una fila y cambia después su depósito. Mira cómo la máquina se queda quieta y la flecha ofrece otra lista.
2. Encuentra las tres. Escribe =CONTAR.SI.CONJUNTO(Tarifas!$B:$B;$B2;Tarifas!$C:$C;$C2)=1 en una columna libre y confirma que vuelven exactamente tres filas con FALSO.
3. Ánclala mal. Escribe la regla hija como =INDIRECTO($B$2), despliega la lista en la fila 9 y lee lo que ofrece. Corrígela a =INDIRECTO($B2) y léela otra vez.
4. Haz crecer la lista. Añade una máquina al final de la columna de un depósito y confirma que la lista la ignora. Da formato de tabla con Ctrl+T, rehaz el nombre sobre la columna de la tabla y confirma que aparece.
5. Cuenta el dinero. Suma la columna F y la columna H de la hoja de ejemplo y cuadra los 4.049,00 € de diferencia: 3.329,00 € son tres parejas que no existen y 720,00 € son una excavadora de 13 toneladas facturada como una de 8.

Para Quedarte Con Esto

En esta hoja no había nada roto. Las listas funcionaban, la ficha de tarifas era correcta, la fórmula de tarifa hizo lo que hace SUMAR.SI.CONJUNTO, y todas las filas habían sido aprobadas por una regla en el momento en que alguien las escribió.

Lo que nadie había escrito en ninguna parte es que esas dos celdas son un solo dato. Un depósito y una máquina son una pareja, la pareja tiene que existir en la ficha de tarifas, y lo único que sigue comprobándolo es una columna que lo pregunta.

=CONTAR.SI.CONJUNTO(Tarifas!$B:$B;$B2;Tarifas!$C:$C;$C2)=1 es esa columna. Cuesta una celda por fila, se pone roja el día en que un trabajo cambia de depósito, y es la diferencia entre un alquiler facturado a 142,00 € al día y un alquiler facturado a nada.