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

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

  1. Gustavo Salazar Longas

    Gracias profesor por esta nueva entrega, es lo que realmente estaba buscando para desarrollar mis tareas de presupuestos. Cordial saludo

  2. Oscar Espinar

    Aunque el ejemplo funciona perfectamente soy incapaz de hacerlo funcionar dentro de una tabla evaluando en cada caso una selección de la misma fila pero tomando los datos a su vez de otra tabla en otra hoja. Para entenderlo en la hoja 1 tengo una tabla en la que en la columna tipo elijo el tipo de material que quiero poder seleccionar de forma filtrada en la columna 2, pero los datos de esos tipos y materiales son a su vez una tabla en otra hoja para poder añadir y quitar con facilidad. Por algún motivo la funcion de lista de validación no me admite usar los nombres de tablas y sus columnas como rangos porque si pruebo con rangos fijos si funciona pero con los nombres de las tablas que son rangos variables da error y no me deja grabar esa formula .

    Alguna idea?

    1. Moisés Ortíz Autor

      Hola Oscar, para crear una lista de validación basada en datos de una tabla se debe crear un Nombre “intermedio”. Este rango nombrado “intermedio” tendrá como origen los datos de la tabla y posteriormente deberás crear la lista de validación utilizando dicho nombre. Por ejemplo, si tengo una tabla llamada Tabla1 (de una sola columna) crearé un nuevo Nombre llamado “MiLista” con el siguiente origen: =Tabla1[#Datos]. Al crear la lista de validación deberé utilizar el origen =MiLista.

  3. Gustavo Salazar Longas

    Mucho Gusto apreciado profesor. Todas sus entregas han sido edificantes para el desarrollo de proyectos en la construcción. Presupuestos

  4. Dia Apli

    Muchisimas gracias por este gran aporte.
    Será de mucha utilidad en mis labores.

  5. INGRID

    Gracias me sirvio muchisimo, me ahorre una gran vuelta que estaba dando

  6. Eduardo

    Que buenisima función, gracias!!

  7. Carlos Lanz

    Excelente tu manual y muy bien aplicado, ya lo implemente en un desarrollo y quedo perfecto gracias

  8. DIEGO

    CONSTANTEMENTE UTILIZO EXCEL COMO MI HERRAMIENTA DE TRABAJO, Y HASTA HOY INGRESO A ESTA PAGINA, HAY APORTES MUY VALIOSOS LOS FELICITO Y AGRADEZCO POR LA AYUDA QUE BRINDAN, ESTARE MOLESTANDOLOS EN EL FUTURO, YA QUE CONTINUAMENTE SE ME PRESENTAN ALGUNOS INCONVENIENTES QUE NO LOGRO RESOLVER.

    MIL GRACIAS

  9. Rocío

    Hola,
    Me sirvió mucho tu artículo, solo una pequeña duda:
    He creado una lista tal y como indicas en tu artículo, pero el tamaño los elementos es demasiado pequeño, se puede modificar el tamaño de la fuente?
    Muchas gracias.

    1. Moisés Ortíz Autor

      Hola Rocío, para este tipo de listas el tamaño de la fuente es el de la celda misma por lo que puedes aumentar o disminuir el tamaño con los botones de la ficha Inicio > Fuente.

  10. Christian

    Muy buena formula y muy bien explicada. Tengo el inconveniente de que el rango al que refiero tanto en la parte del coincidir como del contar si no permanece inmovilizado a pesar de estar con el signo $$.

    por que se mueve? como hago para que no se mueva? es por la forma en que copio hacia abajo?

    ayuda Moises!

    1. Moisés Ortíz Autor

      Hola Christian, Excel jamás “mueve” las referencias absolutas. Una vez que creas una referencia absoluta no importa el método en que se copie o se arrastre siempre permanecerá igual. Es probable que estés creando una referencia mixta, es decir, fijando solo la columna o solo la fila y por eso al copiarla se modifica. Consulta el siguiente artículo que tal vez te pueda ayudar un poco más:
      http://exceltotal.com/utilizando-referencias-absolutas-y-relativas/

  11. LeonardoRave Giraldo

    muy buen articulo, como todos los que he consultado en EXCEL TOTAL, me sirvió muchísimo…

  12. Marcelo Vergara

    Moisés, Muchas Gracias por tus aportes, nos enriquecen profesionalmente a todos tus seguidores. Por otra parte quiero hacer una consulta respecto al mismo tema. Ya ejecuté la lista desplegable en una celda y me resulta bien, pero si hago una lista-validación para un grupo de celdas hacia abajo, se empiezan a desplazar los valores hacia abajo ¿ Como puedo solucionarlo, si se puede ?
    Espero haber sido claro en la explicación. En espera de tu respuesta
    Saludos
    Marcelo V.

    1. Moisés Ortíz Autor

      Hola Marcelo, no me queda muy claro a lo que te refieres con que “se empiezan a desplazar los valores hacia abajo”, pero sospecho que te refieres a un tema de referencias relativas, es decir, que al copiar la fórmula (de la lista) hacia abajo las referencias se desplazan también. Si ese es el problema se resuelve convirtiendo las referencias en absolutas ($), por ejemplo, las referencias de la fórmula mostrada en este artículo habría que cambiarlas de la siguiente manera:
      =DESREF($E$1, COINCIDIR(A1,$D$1:$D$12,0) – 1, 0, CONTAR.SI($D$1:$D$12, A1), 1)

  13. Nestor Rossi

    Hola Moises, en “Cambiar valores de una lista basados en la selección de otra lista” pense que aqui encontraria lo que busco, pero no. Necesito ayuda. Tengo en la hoja1, una base de datos con codigo, articulos, cantidad y precios. En la hoja2, usando buscarv, pido el articulo por medio del codigo para modificar el precio y agregarle mas cantidad.(Valores Numericos) LA PREGUNTA ES: Como reemplazo el nuevo valor del articulo en cuestion, su nuevo precio y sumo la nueva cantidad a la que ya existe que genere en la hoja2??
    Lo que mas se me dificulta es como encuentro el articulo en la matriz y reemplazo los nuevos valores en las celdas correspondientes??
    Muchas Gracias, Nestor Rossi

    1. Moisés Ortíz Autor

      Hola Nestor, ninguna función de Excel te permitirá modificar el valor de otra celda. Las fórmulas devuelven el resultado en la misma celda en que la que se ejecutan. La única manera en que puedes reemplazar los valores de otras celdas es a través de una macro.