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

Cuando creamos listas desplegables en Excel, es común tener un par de ellas que sean dependientes entre sí, es decir una lista cuyos valores dependerán de la selección realizada en otra lista desplegable.

Aunque existe más de una técnica para crear listas dependientes en Excel, hoy utilizaremos las funciones DESREF, COINCIDIR y CONTAR.SI para vincular nuestras listas.

En nuestro ejemplo crearemos la primera lista con los nombres de tres departamentos de una empresa: Finanzas, Marketing y Producción. Una vez que se elija una opción de la primera lista, la segunda lista mostrará los nombres de las personas que pertenecen a dicha área de la empresa.

Listas dependientes en Excel

Nuestra hoja contiene toda la información necesaria para crear nuestras listas desplegables tal como lo puedes ver en la siguiente imagen.

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

Para la técnica utilizada en este artículo, es necesario especificar los elementos de la segunda lista con su respectiva categoría asociada. Para nuestro ejemplo, el rango C1:D12 contiene los nombres de los empleados y el área a la que pertenece cada uno de ellos.

Una vez que tenemos el formato adecuado en los datos, podemos crear nuestra primera lista. Pare ello seleccionaré la celda G1 e iré a la ficha Datos > Validación de datos y crearé la lista desplegable de la siguiente manera:

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

Al hacer clic en Aceptar, la celda G1 mostrará las áreas de la empresa indicadas en el rango A1:A3 de nuestra hoja.

Listas dependientes en Excel

Ahora crearemos la lista vinculada, la cual dependerá de la selección realizada en la celda G1. Ese dinamismo de la segunda lista es lo que nos obliga a utilizar funciones de Excel que nos permitirán especificar el rango de datos origen en base a la selección realizada en la primera lista.

Lista dependiente con DESREF

La función DESREF nos permite crear referencias a un rango de celdas en “tiempo real”, es decir, en el momento exacto en el que las necesitamos sin que la referencia esté previamente definida. Y lo mejor es que sus argumentos nos permiten indicar la celda inicial y final del rango, que es el comportamiento que necesitamos para nuestra lista.

Por ejemplo, si elegimos la opción Finanzas de la primera lista, queremos que la lista dependiente tenga como origen de datos el rango D1:D3.

Listas dependientes en Excel

Pero si elegimos el valor Marketing de la primera lista, entonces queremos que la lista dependiente tenga su origen de datos en el rango D4:D7.

Listas dependientes con la función DESREF

Este comportamiento lo lograremos con la función DESREF y en especial con el uso de su segundo y cuarto argumentos. La fórmula que utilizaremos como el origen de la lista dependiente será la siguiente:

=DESREF(D1, COINCIDIR(G1,C1:C12,0)-1, 0, CONTAR.SI(C1:C12,G1), 1)

Explicación de la fórmula

El primer argumento de la función DESREF es la celda donde comenzará nuestro rango. En este caso será la celda D1 porque ahí están los datos de origen de nuestra lista dependiente.

El segundo argumento de la función DESREF nos permite indicar un desplazamiento en filas a partir de la posición inicial que hemos indicado en el primer argumento. Es decir, si el segundo argumento tiene el valor 5, la función DESREF se moverá 5 filas hacia abajo y nos devolverla una referencia a la celda D6.

En lugar de que el segundo argumento de la función DESREF tenga un número fijo de filas, utilizamos la función COINCIDIR para encontrar la celda donde inician los valores del área indicada en la celda G1. Por ejemplo, si la celda G1 tiene el valor Finanzas, la función COINCIDIR devolverá el valor 1, si se elige el valor Producción, se devolverá el valor 8.

La siguiente imagen muestra el resultado de la función COINCIDIR cuando se ha elegido la opción Marketing de la lista desplegable.

Cambiar valores de una lista dependiente en Excel

Esto quiere decir que Excel ha descubierto que el valor Marketing inicia en la fila 4 del rango C1:C12. Este dato es muy importante porque nos ayuda a indicar a la función DESREF la celda exacta donde comienzan los nombres de los empleados que pertenecen al área seleccionada.

Observa que el segundo argumento de la función DESREF hace la resta del valor 1 al resultado de la función COINCIDIR. Esto es importante para indicar la cantidad exacta de celdas que deberá desplazarse hacia abajo hasta llegar al inicio de los datos.

El tercer argumento de la función DESREF es cero porque no queremos desplazarnos ninguna columna. De esta manera, los primeros tres argumentos de la función DESREF nos sirven para indicar la celda inicial del rango que deseamos obtener.

El cuarto argumento nos permite indicar la cantidad de filas que deseamos incluir en la referencia devuelta por la función DESREF. Para eso utilizaremos la función CONTAR.SI que nos ayudará a obtener la cantidad exacta de empleados que pertenecen a un departamento de la empresa. Por ejemplo, la siguiente imagen muestra el resultado de la función CONTAR.SI una vez que se ha elegido la opción “Producción” en la celda G1.

Cambiar de manera dinámica una lista dependiente en Excel

Este resultado nos hace saber que existen 5 elementos que pertenecen al departamento de  Producción y por lo tanto es el número de filas que deseamos incluir en el rango devuelto por la función DESREF. El quinto argumento de la función DESREF es el valor 1 porque solo quiero obtener una columna.

Crear la lista dependiente

Una vez que comprendemos la utilidad de la función DESREF, podemos crear la lista dependiente, así que seleccionaré la celda G2 e iré a la ficha Datos > Validación de datos para crear la lista de la siguiente manera:

Lista dependiente en Excel con la función DESREF

Observa que he colocado la fórmula como el origen de la lista. Al hacer clic en Aceptar, se habrá creado nuestra lista dependiente en la celda G2 y que cambiará sus valores respecto a la selección realizada en la celda G1. La siguiente imagen muestra los nombres de los empleados que pertenecen al departamento de Marketing.

Cambiar lista en base a la selección de otra lista en Excel

Puedes descargar el libro de trabajo para comprobar que la lista dependiente, ubicada en la celda G2, cambiará sus valores en base a la selección realizada en la lista de la celda G1.

Como lo mencioné al principio del artículo, existen diferentes métodos para crear listas dependientes así que, si quieres conocer otra alternativa para crear este tipo de listas, te invito a leer el artículo Listas desplegables dependientes en Excel.

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

  1. Sergio

    Muy bueno, super explicativo. Gracias

  2. ROBERTO BERRIOS

    Excelente explicación. Necesitaba hacer algo parecido en mi trabajo. Muchas gracias.

  3. Herlan

    Bien. Muy buena la explicación. Felicidades!

  4. Josué [Guatemala]

    Gracias por el conocimiento, también gracias por compartir el libro de trabajo es de mucha utilidad. Saludos

  5. CARLOS PENSADO SANCHEZ

    MIL GRACIAS MOISÉS, ESTA FORMULA PARA SELECCIONAR LISTAS EN FUNCION DE OTRA LISTA ES DE GRAN APOYO EN LAS CONSULTAS

  6. Luis Fonseca Ruiz

    Excelente comando e idea de como aplicarlo, gracias ya aprendí mas. Saludos

  7. Samh

    Excelente, me ha sido de gran ayuda

  8. Eleazar Cruces

    Esta buenísimo el tuto Moises.

  9. gamaliel rodriguez

    Muy buen tip de Excel; gracias.

  10. wally

    Preciso y Conciso. Y demasiado útil. Gracias Moi.

  11. Karla Sánchez

    De nueva cuenta, gracias mi estimado Moi! Excelente tema

  12. Danilo

    Excelente ejercicio. No dejas nada para las dudas. Muchas gracias.
    Danilo

  13. alexander

    es muy interesante y hermoso ver como funciona mi pregunta seria si ingreso personal nuevo en la columna D como lo reconocería automáticamente
    muchas gracias

  14. Violeta Mendoza

    GRACIAS POR COMPARTIR TUS CONOCIMIENTOS.

  15. Raul Ortiz

    Esta enseñanza esta super, ya que te facilita el trabajo al 100%

  16. Edgardo

    Como siempre muy agradecido por tus aportes en todo lo que nos enseña, a menudo recurro a fórmulas para hacer trabajos como el que nos enseña que no lo sabía. Saludos

  17. Lino

    Muy buen tema de Excel Sr. Moisés.

  18. LUCIO

    GRACIAS ES DE GRAN UTILIDAD