Cambiar valores de una lista basados en la selección de otra lista

Supongamos que tienes una lista de departamentos de tu empresa y cada departamento tiene una serie de proyectos. Necesitas colocar dos listas, la primera con los departamentos de la empresa y que la segunda cargue solamente los proyectos del departamento seleccionado. ¿Cómo haces eso en Excel?

Listas dependientes en Excel

La solución para tener listas dependientes en Excel es en utilizar la función DESREF y COINCIDR para determinar el rango adecuado a utilizar. Vamos a asumir que tenemos los siguientes datos:

Cambiar valores de una lista basados en la selección de otra lista

En la columna C tendré la lista de departamentos. En la columna E están en listados todos los proyectos con su departamento correspondiente (en la columna D).
La primera lista validada es muy fácil de crear porque solamente se deben seleccionar los valores del rango C1:C3

Cambiar valores de una lista basados en la selección de otra lista

Para la segunda lista es donde se hará uso de las funciones que permitirán seleccionar un rango específico de acuerdo al valor de la primera lista. La fórmula que se debe colocar es la siguiente:

=DESREF(E1, COINCIDIR(A1,D1:D12,0) - 1, 0, CONTAR.SI(D1:D12, A1), 1)

Parámetros de la función DESREF

La función DESREF toma como base la celda E1 ya que ahí empieza la lista de proyectos por lo tanto la celda E1 es el primer parámetro de la función. El segundo de parámetro de la función DESREF indica la fila donde empezará el rango y por eso se usa la función COINCIDIR para encontrar la fila donde aparece el departamento que se seleccionó en la primera lista.

El tercer parámetro es la columna del rango, pero al necesitar que sea la misma columna colocamos el valor cero. El cuarto parámetro especifica cuántas filas hacia abajo tendrá el rango, por lo que usamos la función CONTAR.SI para determinar cuántos proyectos pertenecen al departamento previamente seleccionado. El último parámetro son las columnas que se incluirán en el rango que en este caso será solamente una, que es precisamente la lista de proyectos.

El cuadro de diálogo de Validación de datos para la segunda lista queda como sigue:

Cambiar valores de una lista basados en la selección de otra lista

Con este último paso hemos creado nuestras listas dependientes en Excel. Al momento de seleccionar un valor de la primera lista que contiene un departamento se llenarán automáticamente los proyectos correspondientes en la segunda lista validada:

Cambiar valores de una lista basados en la selección de otra lista

Consulta más información sobre las funciones utilizadas en esta publicación: DESREF, COINCIDIR, CONTAR.SI