Listas de validación dependientes

 Esta es una de las clásicas preguntas que se hacen en muchos foros y comunidades de Excel: ¿cómo generar listas de validación dinámicas? es decir, que se actualicen dependiendo del valor de otra celda. En este tutorial aprenderás cómo hacerlo. Para comenzar, descarga el archivo que hemos preparado para este tutorial. 

La idea detrás de este archivo es registrar los correos eletrónicos de clientes potenciales de un determinado programador. Con el objetivo de enfocar una campaña de email marketing, el programador decide registrar también el área de trabajo la sub área en la que se desempeña cada cliente potencial. Las áreas y sub áreas se muestran en la hoja Areas

Por otro lado, en la hoja Contactos, el programador registrará los nombres, las direcciones electrónicas, las áreas y sub áreas de cada cliente potencial. El programador ha comenzado ya a ingresar algunos contactos:

El programador desea que en la hoja Contactos, haya una lista desplegable en la columna D para seleccionar las áreas que se encuentran en el rango A2:A8 de la hoja Areas. Esta es una lista de validación normal con la que tal vez ya estés familiarizado. Sin embargo, el programador también necesita que en la columna E haya otra lista desplegable que permita seleccionar las sub áreas correspondientes del rango C2:C48 de la hoja Areas. Por ejemplo, supongamos que Adalberto García trabaja como académico. Entonces en la celda D2 de la hoja contactos, se seleccionará ACA Académico y los valores permitidos de la segunda lista deberán ser:

Ahora supongamos que Daniel Martínez trabaja en el área de Sistemas de Información. Entonces en la celda D3 de la hoja contactos, se seleccionará IT Sistemas de Información y los valores permitidos de la segunda lista deberán ser:

Esto significa que la lista desplegable de la columna E deberá actualizarse automáticamente dependiendo del valor que tome la fila correspondiente de la columna D. Ayude al programador a realizar lo que se propone.

La lista desplegable de la columna D es muy sencilla, ya que sus valores no se actualizarán dinámicamente. Como tal vez ya sabes, la lista desplegable se puede obtener con Validación de Datos. Selecciona la columna D. Ve a Datos -> Validación de Datos. La opción se muestra en la siguiente figura:

Al dar click te aparecerá una ventana que tienes que configurar como se muestra en la siguiente imagen:

Da click en Aceptar. Lo único que hemos hecho hasta ahora es restringir los datos válidos de la columna D de la hoja Contactos a los valores del rango A2:A8 de la hoja Areas. Si seleccionas alguna celda de la columna D verás un pequeño botón. Si das click en él, se desplegará una lista. Si la lista muestra los valores de las áreas, quiere decir que hasta aquí todo va bien:

Ahora pensemos un poco en la situación que tenemos con la segunda lista desplegable. La fórmula que estamos por describir será un poco larga. Tomemos como punto de partida el área de IT Sistemas de Información. Para esta área, los valores que necesitamos están en el rango C34:C36 de la hoja Areas. Recuerda que la fórmula BUSCARV nos dará las primera coincidencia y utilizando la fórmula COINCIDIR podemos saber en qué posición se encuentra tal coindidencia.

Lee también: Fórmulas íNDICE Y COINCIDIR, cuando BUSCARV ya no es suficiente ...

Aún más, podemos utilizar la fórmula DIRECCIÓN para obtener la dirección de la celda de la primera coincidencia. Si quieres experimentar un poco con esto que será la primera parte de la fórmula, puedes seleccionar IT Sistemas de Información en la celda D2 y colocar la siguiente fórmula en la celda G2 de la hoja de cálculo:

=DIRECCION(COINCIDIR(BUSCARV(D2,Areas!B:C,2,0),Areas!C:C,0),3)

Al teclear Enter verá que el resultado es $C$34, ¡que es precisamente la dirección de la primera coincidencia para IT Sistemas de Información en la hoja Areas!

Si eres un usuario avanzado de Excel, tal vez ya puedas intentar continuar por tu cuenta, si no, no te preocupes, aquí te mostramos cómo continuar resolviendo el ejercicio.

Ahora nos enfrentamos a la situación de encontrar cuál es la dirección de la última coincidencia. Esto es posible hacerlo con la misma fórmula que ya usamos, lo único que faltará agregar es la cantidad de sub áreas cuya área es IT Sistemsa de Información. Pero esto lo podemos hacer fácilmente con la fórmula CONTAR.SI. Nuevamente, puedes utilizar la celda G3 para ingresar la siguiente fórmula de prueba:

=DIRECCION(COINCIDIR(BUSCARV(D2,Areas!B:C,2,0),Areas!C:C,0)+CONTAR.SI(Areas!B:B,D2)-1,3)

Al teclear Enter verás que el resultado es $C$36, que es precisamente la dirección de la última coincidencia en la hoja Areas. ¡Ya lo tenemos!.

Lo único que habrá que hacer ahora, será utilizar la función INDIRECTO para obtener los valores del rango de celdas definido por las direcciones contenidas en las celdas G2 y G3. Para visualizar este resultado puedes utilizar una fórmula matricial. Selecciona el rango G5:G8, teclea la siguiente fórmula y presiona la combinación de teclas Ctrl+Shift+Enter:

 =INDIRECTO("Areas!" & G2 & ":" & G3)

Si lo has hecho bien, obtendrás una lista de los valores que serán válidos para la celda D2:

Perfecto. Ahora la pregunta es, ¿cómo integramos esta información en una lista de validación? Muy sencillo. Tenemos que introducir esta fórmula en la ventana de Validación de Datos. Solo hay un par de consideraciones que debemos notar.

Aunque requiere un poco más de cuidado para hacerse, lo mejor es colocar todo en la ventana de Validación de Datos sin utilizar celdas auxiliares. En este ejemplo, esto quiere decir que las fórmulas que tenemos en G2 y G3 se tendrá que integrar con la fórmula de G5 y todas ellas deberán ser ingresadas en la ventana de Validación de Datos. En este ejemplo, la validación de datos aplica para toda la columna E, por lo que es necesario modificar un poco las fórmulas de DIRECCION que vimos anteriormente. Tomando en cuenta todas estas consideraciones, aquí tienes la fórmula completa en todo su esplendor:

=INDIRECTO("Areas!" & DIRECCION(COINCIDIR(BUSCARV(D1,Areas!B:C,2,0),Areas!C:C,0),3) & ":" & DIRECCION(COINCIDIR(BUSCARV(D1,Areas!B:C,2,0),Areas!C:C,0)+CONTAR.SI(Areas!B:B,D1)-1,3))

Observa las modificaciones que se hicieron con respecto a las anteriores. Copia esta fórmula, en Excel selecciona toda la columna E, ve a Datos -> Validación de Datos. En la lista desplegable Permitir selecciona Lista y en origen perga la fórmula tal cual está aquí. Así debería verse la ventana:

Es importante aclarar que el control de fórmula es un poco truculento de manejar ya que no puedes desplazar el cursor con las flechas de dirección. Si lo haces terminarás con una fórmula totalmente diferente a la deseada. Lo mejor es como hemos hecho aquí, armar la fórmula directamente en Excel y posteriormente solo copiar y pegar en la ventana de Validación de Datos.

Esto es todo, Aquí te muestro algunas imágenes de cómo debería haber quedado:

Independientemente de cómo te haya ido en este tutorial, tal vez te interese mostrar la hoja oculta del libro. En esta hoja verás el ejercicio resuelto tal como lo hemos hecho aquí.

Agradezco que dejes un comentario. Si necesitas alguna respuesta, por favor deja tu correo en el cuerpo del mensaje. Gracias!