jueves, agosto 11, 2011

Lista desplegable con actualización automática

Sigo saldando las deudas con mis lectores y en esta oportunidad cómo crear dinámicamente una lista desplegable con valores únicos.

La situación es la siguiente: creamos una tabla de facturas en una hoja de Excel con los campos “fecha”, “Cliente” y “Total”.



El campo “Cliente” contiene una lista desplegable creada con Validación de Datos. El objetivo es que el usuario introduzca el nombre del cliente eligiéndolo de la lista desplegable.

Para eso necesitamos crear una lista desplegable que se actualice automáticamente cada vez que agregamos un registro con un nuevo cliente en la base de datos.

Supongamos que la los registros únicos de la lista desplegable los ponemos en la columna H (en un modelo real el rango de la lista desplegable estaría en una hoja oculta). Nuestro objetivo es actualizar la lista cada vez que ingresemos un cliente nuevo.



Para actualizar la lista desplegable vamos a programar un evento. Como verán a continuación el código es muy sencillo ya que vamos a usar la herramienta Filtro Avanzado para crear una lista de valores únicos.
Empecemos por señalar que el rango de la base de datos lo definimos como Tabla (Lista en Excel 2003), de manera que la validación de datos del campo Cliente (la columna B) se agregue en forma automática cada vez que agregamos un registro en la base de datos.

Veamos como funciona el modelo:



1 - Creamos un nombre que se refiere a un rango dinámico con la fórmula

=DESREF('base de datos'!$H$1,1,,CONTARA('base de datos'!$H:$H))



Esta fórmula genera un rango dinámico donde el último valor del rango es una celda vacía. Esto es necesario para evitar que la validación de datos nos impida ingresar un cliente que aún no encuentra en la lista de clientes. Obviamente, esto cancela la validación de datos, ya que al haber una celda vacía todo valor que ingresemos será valido, pero nos permite crear el efecto de actualización automática.

2 - Programamos un evento de hoja Worksheet_SelectionChange con este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("C:C")).Address = Range("C:C").Address Then
        Range("H:H").ClearContents
        Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=Range("H1"), Unique:=True
    End If

End Sub


En este código el evento se produce cuando seleccionamos una celda en la columna C, es decir, después de haber ingresado el cliente en la columna B.

Si queremos que la lista desplegable aparezca ordenada alfabéticamente podemos en el evento una llamada a este código (que va en un módulo común del Vbe)

Sub ordenar_clientes()

    Range("H1").CurrentRegion.Sort Key1:=Range("H2"), _
                Order1:=xlAscending, Header:=xlYes _
                , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                 DataOption1:=xlSortNormal

End Sub


El código del evento en el módulo de la hoja Base de datos quedaría

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("C:C")).Address = Range("C:C").Address Then
        Range("H:H").ClearContents
        Range("B:B").AdvancedFilter Action:=xlFilterCopy, _
                            CopyToRange:=Range("H1"), Unique:=True
    End If

    Call ordenar_clientes
  
End Sub



Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

30 comentarios:

  1. Buenos días

    Podrías mostrar las fórmulas completas que aparecen en la captura del Administrador de nombres. En algún paso me he perdido y sería de mucha ayuda si se pudiesen viasualizar completamente esas expresiones.

    Una vez más, como decía Rosendo Mercado SORPRENDENTE.

    Muchas gracias

    Saludos

    ResponderBorrar
  2. Muy bueno el blog, siempre se aprende algo nuevo de excel. En este caso, creo que hay una forma más sencilla de hacerlo, aunque no tan profesional. Cuando se elige el rango de la lista, se elige desde el primer registro hasta el final de la planilla. De esta manera, cada vez que se agrega un registro, queda dentro del rango definido.

    ResponderBorrar
  3. Los nombres relacionados a la tabla son creados automáticamente por Excel. El nombre que define el rango de la lista desplegable eaparece exprésamente en la nota.

    ResponderBorrar
  4. No tengo claro a qué rango te refieres. Si se trata de la columna B, los clientes aparecerían repetidos en la lista desplegable.

    ResponderBorrar
  5. Me refiero al rango donde están los nombres de los clientes, en el ejemplo sería la columna h. Por ejemplo, eligiría el siguiente rango: h2:h1000. Al principio, en la lista desplegable aparecerían los 7 clientes que están y muchos espacios en blanco (éste es el inconveniente que le veo), pero al ir agregando nombres de clientes en la columna h, se incorporan automáticamente a la lista. No sé si ahora quedó más claro...

    ResponderBorrar
  6. El rango de la lista de clientes se ajusta automáticamente, así que la lista desplegable no muestra espacios en blanco (excepto el que dejamos para anular la validación de datos).
    En este blog hemos tratado extensamente el tema de rangos dinámicos. Puedes hacer una búsqueda en las notas bajo la etiqueta Listas Desplegables.

    ResponderBorrar
  7. Hola Buen Dia tengo que realizar varias listas deplegables dentro de una misma base es decir elijo de la 1er lista con 3 posibles opciones;por decir algo la planta de trabajo en base a esta lista y a la planta elegida necesitare que me proporcione una 2da lista desplegable que proporcione informacion condicionada independiente de las otras dos plantas esto lo hecho agregando una funciones SI(x)al campo de validacion,el problema viene cuando me solicitan una 3ra lista el limite de espacios en la validacion me impide continuar haciendo funciones Si hay alguna forma de hacer esto de manera mas simple e visto algunos trbjaos de excel donde dan una lista desplegable larga donde en base a la condicion ke das en la celda anterior la lista se abre apartir del mismo criterio podrian ayudarme con esto?

    ResponderBorrar
  8. Gracciano Hernández16 septiembre, 2011 02:06

    Excelente blog, saludos desde Venezuela. Tengo una duda y quisiera plantearte que de se seguro podrás ayudarme...

    Tengo una lista que cree con Validación de datos, en esta lista aparece unos modelos de telefonos, lo que necesito es que cuando seleccione un modelo en especifico, me muestre el precio automáticamente en una celda, cómo puedo hacerlo?

    Otra duda que tengo: cree un botón para borrar el contenido de las celdas, pero hay celdas que tienen formulas, al presionar el boton tambien borran las formulas y necesito que esto no ocurra, solo necesito que me ponga las celdas en blanco y cada celda quede formulada como estaba.

    Espero pronta respuesta! ;) gracias!

    ResponderBorrar
  9. Vamos por partes:
    Para que el precio aparezca al seleccionar un valor en la lista desplegable tienes que crear una fórmula con BUSCARV(VLOOKUP) o cualquier otra función de búsqueda, en la celda donde quieras que aparezca el precio.
    Obviamente, no tienes que borrar las celdas que contienen fórmulas, sino las celdas que las alimentan. Al no haber valores en las celdas que alimentan la fórmula, el resultado de estas últimas será 0 o #VALOR o #NA. Puedes ocultar los valores de error usando SIERROR si usas Excel 2007 o 2010, o con alguna otra técnica (función SI o formato condicional).

    ResponderBorrar
  10. Muy interesante.

    Me han comentado de hacer en una lista desplegable y poder escoger varias de las opciones con un check dentro de la misma lista.

    ¿Esto es posible, incluso pasando por VBasic? ¿y en la celda se verian las opciones separadas por "," o lo que se quisiera?

    Saludos

    Arnau

    ResponderBorrar
  11. Se hace usando el control Cuadro de Lista (listbox) de la colección de controles ActiveX. Y si, requiere usar Vba.

    ResponderBorrar
  12. Buenas tardes,
    Excelente solución para las Macro DB!!!
    He realizado pruebas con tu ejemplo, pero solo era possible es la misma hoja!
    Si el rango de la lista desplegable estuviese en una 2º hoja, que parametros abria que modificar en formula y código de evento ???
    Gracias de antemano por tu ayuda .

    ResponderBorrar
  13. No hay ningún problema en definir la lista en otra hoja, que es lo que pongo en la nota ("en un modelo real el rango de la lista desplegable estaría en una hoja oculta). Por ejemplo, si ponemos en la hoja "lista" la lista de clientes, el rango dinámico se referiría a la fórmula
    =DESREF(lista!$H$1,1,,CONTARA('base de datos'!$H:$H))

    ResponderBorrar
  14. Despues de realizar varios test sigo sin poder actualizar la 2º hoja. Me copia los datos en la misma hoja (1º), creo que se deve al codigo evento. Alguna sugerencia de como indicarle que copie los datos en la 2º hoja - a mi me da siempre error '1004'.

    ResponderBorrar
  15. MSN,
    te sugiero que me mandes tu archivo (la dirección aparece en el enlace Ayuda, en la parte superior de la plantilla) con una descripción de lo que quieres hacer. Seguimos el diálogo por línea privada.

    ResponderBorrar
  16. Hola Jorge
    Una consulta como le agregas a una validación con lista, un autocomplete, es decir que puedas escribir 2 o 3 letras y te vaya sugiriendo los datos de la lista que vayan coincidiendo?

    Un saludo
    Macalister

    ResponderBorrar
  17. quiero hacer esto
    cliente(lista Desplegable, Tipo(lista desp) Cantidad (la suma de las cantidades segun el cliente y el tipo, esa es la tabla ejemplo

    cliente tipo cantidad
    aaa 123 xxx
    bbb 345 xx
    ccc 345 xx
    ddd 123 xxx
    bbb 345 xxx


    muchas gracias por su colaboracion

    ResponderBorrar
  18. Te sugiero que hagas una búsqueda en el blog con el valor "listas desplegables dependientes". Hay varias notas que tratan el tema.

    ResponderBorrar
  19. Estimado Jorge :
    Tu blog es para mi como la biblia para los cristianos...

    Soy fanatico de excel y trato de hacer todo con el.

    Tengo un libro con dos hojas Registros y clientes, en registros una columna con nombres (lista desplegable que hace referencia a la hoja clientes usando NOMBRES definidos) la hoja clientes tiene espacio para 1.000 clientes pero solo tiene 20.

    El problema es que la lista desplegable en la hoja registros al momento de hacer click siempre me sale en blanco, los 20 clientes me los da pero tengo que subir la lista para poder verlos, mientras mas clientes agrego los nombres suben y solo me van a pareciendo los espacios en blancos que quedan. espero ser claro, saludos

    ResponderBorrar
  20. Boris,
    se hace usando rangos dinámicos, como el que uso en esta nota. Si no queda clar, podés hacer una búsqueda en el blog con el valor "rangos dinámicos" para ver las notas donde trato el tema.

    ResponderBorrar
  21. Buenas tardes, Jorge.
    Te escribo para ver si me puedes iluminar sobre el mundo excel. Tengo creado un libro donde se anotan las operaciones de unos contratos ("enero contratos", y así sucesivamente con los 12 meses). Luego, tengo creado una hoja de excel con un formato de factura. ¿Sería posible que una vez voy rellenando los datos en "enero contratos" se pasen a la factura?
    Gracias.

    ResponderBorrar
  22. Hay varias formas de hacerlo. Por ejemplo, podrías por ejemplo, usar las funcines de búsqueda (BUSCARV, INDICE, etc.) en la plantilla de la factura para extraer los datos. Pero,¿por qué separar los datos por hojas en lugar de ponerlos en una única hoja?

    ResponderBorrar
  23. Saludos
    Y cuando programamos un evento de hoja para que la lista quede en otra hoja, ¿cómo sería éste?

    ResponderBorrar
  24. Creando la referencia a la hoja en

    CopyToRange:=Sheets(nombre de la hoja).Range("H1")

    ResponderBorrar
  25. URGENTE PLIS......A LOS GENIOS DE EXCEL......
    Hola a todos, soy nueva en este tema y estoy complicada con algo que me solicitaron y solo cuento con ustedes para solicitar ayuda......Desde ya gracias a los que puedan ayudarme....

    Me han solicitado crear una lista desplegable con datos que vienen de dos hojas distintas, la idea es que esta lista no muestre valores que esten en ambas hojas,...o sea, ........
    ......por ejemplo si Carlos esta en hoja1 y hoja2...NO DEBE APARECER EN LA LISTA DESPLEGABLE

    Desde ya gracias a los que puedan ayudarme.....
    ISABELS


    ResponderBorrar
  26. Hola, en primer lugar te sugiero que pongas tu consulta en alguno de los muchos y buenos foros de Excel que hay en la Internet. Este es un blog y los comentarios están destinados a eso, a comentar.
    De todas maneras, te sugiero ponerte en contacto conmigo pro mail privado (fijate en lo que pongo en el enlace Ayuda, en la parte superior del blog).

    ResponderBorrar
  27. No entiendo esta línea de código, a mi me da error

    If Union(Target, Range("D:D")).Address = Range("D:D").Address Then

    me da error de sintaxi. Me podrias ayudar

    ResponderBorrar
    Respuestas
    1. Tendría que ver todo el contexto. ¿Que dice el mensaje del error?

      Borrar

Nota: sólo los miembros de este blog pueden publicar comentarios.