Lista desplegable con datos de otra hoja en Excel

Las listas desplegables en Excel son esenciales para facilitar al usuario la elección de valores y mantener la consistencia de nuestros datos. En ocasiones necesitamos crear listas desplegables que tengan como fuente los datos ubicados en otra hoja de Excel.

En este artículo analizaremos, en primer lugar, la creación de una lista desplegable utilizando validación de datos y posteriormente analizaremos la solución utilizando un control de formulario.

Lista desplegable con referencia a otra hoja

Si utilizamos una lista de validación de datos podemos hacer referencia a los datos de otra hoja al momento de indicar el criterio de validación. En la siguiente imagen puedes notar que estoy creando una lista (en la Hoja2) que hace referencia al rango de A2:A13 de la Hoja1:

Lista desplegable con datos de otra hoja en Excel

Como resultado obtendremos una lista desplegable con los valores contenidos en una hoja diferente:

Lista desplegable desde otra hoja de Excel

Lista desplegable con datos de una tabla

En el ejemplo anterior creamos una lista desplegable que hacía referencia a un rango de celdas, pero es muy común que nuestros datos estén contenidos en una tabla. Supongamos que los datos de la Hoja1 tienen formato de tabla:

Crear una lista desplegable con datos de otra hoja

Como sabes, al crear una tabla en Excel tendrá un nombre asignado que podremos consultar desde el administrador de nombres. De manera predeterminada Excel nombrará las tablas de nuestro libro como Tabla1, Tabla2, Tabla3, etc. Para crear una lista desplegable con los datos de la columna Meses tenemos que crear un nombre intermedio que haga referencia a la columna que deseamos considerar así que abriré el administrador de nombres (en la ficha Fórmulas) y pulsaré el botón Nuevo. Observa con detenimiento la referencia que colocaré para el nombre ListaDesplegable:

Lista desplegable en Excel con datos de una tabla

A la referencia Tabla1[Meses] se le conoce como referencia estructurada y es la manera en que podemos referirnos a los datos contenidos en una tabla de Excel. Ahora que hemos creado este nombre intermedio podemos crear nuestra lista de validación de datos utilizando como fuente este nuevo nombre:

Lista de validación con datos en otra hoja de Excel

Al momento de seleccionar el cuadro de texto Origen puedes pulsar la tecla F3 y se mostrará el cuadro de diálogo Pegar nombre o simplemente puedes introducir manualmente el nombre recién creado. Ya que los nombres tienen un alcance en todo el libro podemos crear nuestra lista desplegable con datos de otra hoja sin la necesidad de indicar directamente la referencia al rango que contiene los datos.

Este truco del nombre intermedio es necesario porque Excel no permite colocar la referencia estructurada directamente en el Origen de la lista de validación de datos. Si intentas hacerlo Excel devolverá un Error en la fórmula.

Lista desplegable con Control de formulario

Otra manera de crear una lista desplegable en Excel es utilizar el control de formulario llamado Cuadro combinado. Una vez que agregamos este control a nuestra hoja solo debemos hacer clic derecho sobre él y seleccionar la opción Formato de control y en la sección Control seleccionaremos el Rango de entrada adecuado:

Cuadro combinado con datos de otra hoja

Lo único que estoy haciendo es colocar la referencia adecuada hacia la hoja donde se encuentran los datos que deseo incluir. Como resultado obtendremos una lista desplegable con datos de otra hoja:

Lista desplegable con datos de origen en otra hoja

Recuerda que para conocer la opción que ha sido seleccionada en este tipo de control de formulario debemos vincular el cuadro combinado a una celda la cual reflejará la opción seleccionada. Para conocer un poco más sobre este procedimiento consulta el artículo Cuadros combinados en Excel.

En este artículo hemos analizado tres posibilidades para crear una lista desplegable con datos de otra hoja de manera que no sea necesario tener tanto los datos como las listas desplegables en una misma hoja. Esta es una práctica muy común, especialmente cuando tenemos múltiples catálogos organizados en diferentes hojas de nuestro libro y necesitamos crear las listas desplegables en una hoja diferente.

Artículos relacionados
Listas dependientes en Excel
Valores de una lista basados en otra

47 pensamientos en “Lista desplegable con datos de otra hoja en Excel

  1. EDISON

    Definitivamente fue un acierto poder encontrado esta gran ayuda, cada vez aprovechamos mejor de las bondades y facilidades que nos brinda el Excel, pero mucho mejor es la oportunidad que nos brindan los expertos al compartir su experiencia y hacen más cómoda nuestra gestión con hojas electrónicas y las aplicaciones múltiples que podemos realizar en nuestras labores

    Responder
  2. Ana

    Gracias Moisés, esto es justo lo que estaba buscando …

    Responder
  3. Walter Gregorio Toro Lopez

    Buenos Días
    Muy contento de haber encontrado esta pagina, espero aprender mucho de excel apenas estoy comenzando me parece una super herramienta
    Mil gracias por los conocimientos que estoy adquiriendo con ustedes

    Responder
  4. otrebor

    Cada día nos sorprendes, a veces uno cree saberlo todo respecto a una temática y llega tu e-mail y nos damos cuenta que en realidad nos falta mucho por aprender.
    Agradecido por tus aportes.

    Responder
  5. RAUL

    Muy agradecido de haber encontrado esta pagina la informacion que nos suministra es muy valiosa

    Responder
  6. Carlos Castillo

    Simplemente excelente, NO dejes de enseñar, Maestro. Gracias

    Responder
  7. BEATRIZ MAUNA

    CORDIAL SALUDO FELICITACIONES LOS TEMAS QUE ME HAN ENVIADO MUY INTERESANTES HE APRENDIDO BASTANTE YA QUE SON MUY EXPLICITO Y FACILES DE ENTENDER

    ATTE

    BETTY

    Responder
  8. Omar

    Moises buenos dias te felicito por la explicacion con respecto a la lista despegable y sus variedades que tiene Excel sos lo maximo, hasta el momento los mail recibidos con respecto de Excel me es muy util inclusive me facilito en mis tareas diarias laborales al estar muy bien explicadas desde ya graciasss….

    SALUDOS
    OMAR

    Responder
  9. Jorge

    Impecable!

    Felicitaciones

    Responder
  10. Fabricio

    Saludes,mis conocimientos en excel van en ascenso, esto se pone muy interesante

    Responder
  11. RUTH

    CADA VEZ VOY APRENDIENDO MAS, GRACIAS A LA AYUDA QUE NOS BRINDAN. ESPERO SEGUIR CONTANDO CON ESTA VALIOSA COLABORACION.

    Responder
  12. Art

    Muchas gracias! Muy útil!

    Responder
  13. Luis Carlos

    Excelente aporte, para todos los que aún debemos aprender más de Excel. Gracias.

    Responder
  14. Marcos Barragán

    Moisés muchas gracias por el aporte, tus consejos prácticos han sido de gran ayuda, necesito hacer una lista desplegable para una serie de productos y que una vez seleccionado un producto en otras celdas que me especifique las características del producto seleccionado, como precio, contenido, etc

    Responder
  15. Walter

    Gracias Moises… siempre sigo tus publicaciones y cada vez me sorprendo más con todo lo que se puede hacer con esta fabulosa herramienta.

    Responder
  16. dalvi

    Hermano de verdad agradecido, estoy siempre en la búsqueda de manejar a la perfección todas las bondades de excel…

    Responder
  17. Percy Cansaya

    Gracias por los aportes. Estan muy buenos.

    Responder
  18. jeans

    GRACIAS POR LA AYUDA, EXCELENTE

    Responder
  19. Alejandro

    Tengo una Lista desplegable con Control de formulario tomo los datos de otra hoja como puedo hacer para que verifique que el usuario selecciono un dato y no lo dejo vacío.

    Responder
    1. Moisés Ortíz Autor

      Hola Alejandro, debes hacerlo con una macro validando que el valor de la celda no sea blanco.
      If (Range(“A1″).Value = “”) Then
      MsgBox (“Debes introducir un valor”)
      End If

      Responder
  20. Luis

    Gracias Moises, muy util… y practico

    Responder
  21. Carlos Becerra

    Amigo Moises.
    Gracias por compatir, cada día me sorprende las ventajas de excel.

    Responder
  22. Edwin Yash

    Gracias por el tiempo utilizado para enseñarnos
    fue muy util…….

    Responder
  23. Sofía

    Hola,

    Muchas veces visito vuestra página que es realmente útil pero nunca había comentado nada… pero de verdad que hoy necesito vuestra ayuda.
    Esto de la lista desplegable lo he utilizado bastantes veces, pero ahora me he instalado office 2013 y si intento copiar una lista desplegable en varias celdas de la misma columna no me deja fijo los valores, si no que va desplazando la lista al mismo tiempo que vas bajando filas.
    ej. Si pongo =Datos!$E3:$E6 en la celda A2 y arrastro la fórmula a la celda A3, se me cambia a =Datos!$E4:$E7

    Además si he hecho una lista desplegable con excel 2010 en el trabajo y la abro en casa con excel 2013 se me desconfigura…

    ¿Os ha pasado esto alguna vez? ¿Podéis ayudarme?
    Muchísimas gracias

    Responder
    1. Moisés Ortíz Autor

      Hola Sofía, la referencias que muestra solo está fijando la columna, así que te recomiendo fijar también la fila de la siguiente manera:
      =Datos!$E$3:$E$6

      Responder
  24. JM

    Manda “webs” que sea mejor esta página que la ayuda oficial de Office para Excel.
    Lo mejor es que está muy bien explicada; lo sorprendente es lo que sabe de Excel.
    Saludos

    Responder
  25. Azulfenix

    Gracias por tu genial ayuda.
    En mas de una ocasion me has sacado de un problema.

    Una vez gracias por compartir.

    Responder
  26. Irma

    Te agradezco mucho lo que me enseñaste. Soy una inexperta pero con tu ayuda haces más fácil mi trabajo en Excel.

    Responder
  27. Wendy

    Las listas desplegables me han ayudado muchísimo en la confección de facturas en Excel 2007.
    He tenido que aumentar la lista donde ahora tengo 21 líneas, pero tengo problemas porque cuando la despliego solamente aparecen las primeras 17 líneas.
    Qué se puede hacer para que aparezcan todas las líneas de la lista???
    Como info adicional tengo la lista desplegable en la hoja 2 y preparo la factura en la hoja 1, donde utilizo esta lista.
    Agradeceré su respuesta.

    Responder
    1. Moisés Ortíz Autor

      Hola Wendy, 21 elementos son muy pocos y deberían mostrarse en la lista. ¿Cómo estás especificando el origen de la lista?

      Responder
  28. José Castillo Pérez

    Moisés, Primero que todo te felicito por tus importantes aportes a nuestros conocimientos, soy amante de excel y pienso que manejo lo básico, pero es una de las mejores páginas que he visto con relación a Excel…. Muchísimas gracias….

    Responder
  29. ARIEL SHADE

    Moisés:
    Ante todo excelente la pagina, mi consulta es la sig.: tengo 3 listas desplegables relacionadas entre si con la funcion indirecto: la primera es lugar fisico, la seguda el dpto. y la tercera la seccion. Cuando voy eligiendo diferentes opciones me va limitando la seleccion, hasta ahi todo perfecto, el tema es que cuando modifico la opcion “lugar fisico” me mantiene las otras opciones elegidas, es decir, no se limpiar los demas campos automaticos. Hay alguna forma?

    Responder
    1. Moisés Ortíz Autor

      Hola Ariel, solo se puede lograr con VBA. Por ejemplo, si la primera lista está en A1 y queremos eliminar el valor de las otras listas (en B1 y C1) cuando haya un cambio, entonces puedes utilizar el siguiente código:

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Target = Range(“A1″) Then
      Range(“B1″).Value = “”
      Range(“C1″).Value = “”
      End If
      End Sub

      Responder
  30. Juan

    Muy bien; excelente aporte, y gracias por tu comentario Moises

    Responder
  31. Jorge Aldana

    Hola mi estimado Moisés, oye con respecto al primer ejemplo de validación de una lista, porque en mi Excel no me permite hacer validación de una pestaña a otra. A que se debe?

    Responder
    1. Moisés Ortíz Autor

      Hola Jorge, la verdad no te podría responder porque desconozco el error que te devuelve Excel, pero es muy extraño porque las referencias a otras hojas son un comportamiento básico de la herramienta.

      Responder
  32. Pedro Espinoza

    Buen día Moisés,
    Quiero hacer una lista de excel con una lista desplegable de otra hoja pero con al menos 2 celdas de datos, pero quisiera que al momento de poner un valor inmediatamente se llene la celda continua con los datos, como le puedo hacer?

    Responder
  33. Roberto

    Estimado una consulta, hay alguna forma de realizar esta lista despegable de varios tipos en una sola columna?
    por ejemplo:
    la cela A1 dice Civil.; desde la celdas A2:A15 tods las actividades de civil
    La celda A16 dice electrico; desde la A17:A30 todas las actividades de electrico.
    se puede?

    Responder
    1. Moisés Ortíz Autor

      Hola Roberto, no me queda claro a lo que te refieres con “una sola columna”, pero nada impide crear una lista basada en el rango A2:A15 y otra lista basada en el rango A17:A30.
      Si quieres que toda una columna sean listas desplegables, solo es necesario que crear la primer lista y posteriormente copiar/pegar en las celdas hacia abajo, aunque por supuesto, no podría ser sobre la misma columna que tiene el origen de los datos.

      Responder
  34. Victor Juarez

    Puedo crear una lista desplegable basado en el resultado de otra lista desplegable, es decir tengo 10 opciones y cada una de ellas tiene diez opciones, lo que yo quiero es que si elijo la opcion uno en mi lista desplegable-1, me aparezcan las 10 opciones del numero 1 en mi lista desplegable-2, si eligo la opcion 5 en la lista desplegable-1, me aparezan en la lista desplegable-2 las 10 opcionens del numero 5. No se si me explique correctamente.

    Responder
  35. Eduardo

    Tengo 126 nombres de municipios; y por cada registro que capturo, el nombre del municipio es un campo que forma parte del registro. A esta lista de 126 municipios les puse una clave del 1 al 126, con la finalidad de que en lugar de mecanografiar el nombre completo del municipio, baste con anotar la clave numérica correspondiente.
    Requiero que se desplieguen como lista los 126 municipios ejm.
    1 – Amecameca
    2 – Almoloya de Juárez
    3 – Calimaya

    126 – Xonacatlan
    pero me permita registrar únicamente la clave del municipio y no la clave y el nombre de este. Como lo puedo hacer ??
    Mucho agradeceré su ayuda.

    Responder
    1. Moisés Ortíz Autor

      Hola Eduardo, eso no lo podrás lograr solo utilizando una lista de validación de datos, tendrás que incluir programación VBA. Te recomiendo utilizar el control ActiveX “Cuadro combinado” y hacer uso del índice de cada elemento de la lista para conocer el elemento seleccionado por el usuario.
      http://exceltotal.com/cuadro-combinado-en-vba/

      Responder
  36. javier

    Quisiera saber si es posible que al desplegar la lista desplegable se muestren todos los datos existentes en el listado. (osea que cuando pulse la flechita de la derecha existente en la celda, en el caso de que halla muchos datos listados, los muestre todos, no tenga que bajar con la barra lateral)
    Gracias de antemano y un saludo

    Responder
    1. Moisés Ortíz Autor

      Lo siento Javier, eso no es posible. La única alternativa sería utilizar un control ActiveX en lugar de la lista de validación de datos ya que permite indicar la cantidad de elementos a desplegar. Puedes consultar el siguiente artículo para saber un poco más sobre ese control:
      http://exceltotal.com/cuadro-combinado-en-vba/

      Responder

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>