Listas desplegables con contenido condicional.

domingo, julio 29, 2012

Supongamos una lista de artículos que contiene dos columnas: el código del artículo y el estatus (activo o inactivo). ¿Cómo hacemos para crear una lista desplegable que muestre solamente los artículos activos?

Con anterioridad a la introducción de las “listas” en Excel 2003, luego rebautizadas “tablas” en Excel 2007/10, lo hubiera hecho con una macro. Posiblemente extrayendo los valores que cumplen con el criterio usando Filtro Avanzado y creando un nombre que se refiera a ese rango dinámicamente. Finalmente, usaríamos una macro para automatizar el proceso.

Pero podemos aprovechar las funcionalidades de las tablas para crear un modelo sin macros.

Supongamos que esta es nuestra lista



Vamos a usar una variante de la técnica que mostré en la nota que trató sobre cómo agregar valores únicos a una lista desplegable.

Empezamos por convertir la matriz en “tabla”



Ahora agregamos una columna auxiliar (“Aux1”) con esta fórmula

=SI(B2="Activo",A2,"")



El próximo paso es crear la columna “Aux2” que contiene la fórmula

=SI(CELDA("contenido",C2)="","",FILA())

Lo que hace esta fórmula es evaluar si el resultado de la fórmula es vacío (la celda no está vacía; contiene una fórmula); en caso afirmativo da un resultado vacío, en casi negativo muestra el número de código del artículo.



En esta columna obtenemos un número de orden para los artículos con estatus “activo”.

Finalmente creamos la columna auxiliar “Lista” donde aparecen los artículos activos ordenados por orden de aparición en la tabla. Esto lo hacemos con la fórmula

=INDICE($C$2:$C$15,COINCIDIR(K.ESIMO.MENOR($D$2:$D$15,FILA()-1),$D$2:$D$15))



Como estamos trabajando con una tabla, al agregar un nuevo artículo todas las fórmulas son copiadas automáticamente.

Lo último que nos queda por hacer es crear una nombre que se refiera dinámicamente al rango de la columna Lista en la tabla que no contiene valores #NUM!.

En el administrador de nombres definimos el nombre “ListaArticulos” que se refiere a la fórmula

=DESREF(articulos!$E$2,0,0,SUMAPRODUCTO(--NO(ESERROR(Tabla1[Lista]))),1)



Ahora podemos crear la lista desplegable usando Validación de Datos—Lista



Este video muestra como se adapta el contenido de la lista desplegable a los cambios en la tabla



El archivo con el ejemplo se puede descargar aquí.

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.

16 comments:

Jose Riu,  30 julio, 2012 17:01  

Buen dia Jorge. me parece que se te deslizo un error al referirte a la celda "G2", ya que deberia ser "C2", asi como tambien con el rango "$G$2:$G$15", que deberia ser
"$C$2:$C$15".
Solo un error de tipeo.
Gracias por la info. saludos.

Jorge L. Dunkelman 30 julio, 2012 18:50  

José,

tenés razón. Gracias por la observación. Acabo de corregir los errores.

Anónimo,  02 agosto, 2012 18:59  

Jorge, felicidades por tu trabajo, sabes como hacer que las celdas que tienen la formula buscarv mantengan los hiperenlaces??? imagina que uso buscarv para que me de un email, el resultado muestra la direccion sin hipervinculo.

Gracias

Carlos Véliz 03 agosto, 2012 04:20  

Estimado Jorge, ¿Cómo lo puedo hacer para personalizar mi Excel de forma tal que al iniciar el programa asuma una letra de color azul por defecto, sin tener que elegir una plantilla determinada?
Saludos,

Carlos

Gustavo A. Sebastiani cépeda 03 agosto, 2012 17:19  

al igual que tu, quien te escribe tiene su web de Excel en la cual comparto conocimientos, tomare algunos ejemplos de tu web que me parece muy buena para compartirlo. tu trabajo es muy bueno.

Jorge L. Dunkelman 03 agosto, 2012 18:01  

Gracias Gustavo. Mandame el enlace a tu web por mail privado (aparece en el enlace Ayuda). Me gustará ver lo que publicas.

Jorge L. Dunkelman 06 agosto, 2012 18:45  

Carlos, se puede crear una plantilla, es decir, un cuaderno con todos los formatos requeridos y guardarlo como plantilla (xxx.XLT). Esta plantilla hay que guardarla en la carpeta ...\XLSTART.
Para usarla hay que abrir el nuevo cuaderno con Nuevo (Ctrl+N), con con Abrir (Ctrl+A)

Anónimo,  23 septiembre, 2012 20:17  

Mi solución para este problema pasa por la utilización de offsets... me parece una solución mas sencilla

Aun así buena solución

Carlos Estremadoyro 30 octubre, 2012 01:40  

Hola
Muy bueno tu blog, siempre lo consulto.
Una consulta, hice el mismo ejemplo pero generando 2 listas una para Activos y otra para Inactivos, genero los nombres dinámicos Lista_Activos y Lista_Inactivos, genero una primera lista desplegable en $H$1 con elementos Lista Activos / Lista Inactivos, hasta ahi todo perfecto, mi problema viene en la segunda lista desplegable que dependiendo de lo seleccionado en $H$1 me muestre una de las 2 listas, uso =Indirecto(Sustituir($H$1," ","_")), pero me bota error y no se abre la lista, probé de todo y nada, te agradecería si me puedes dar una mano

Gracias

Carlos

Jorge L. Dunkelman 30 octubre, 2012 20:10  

Carlos, te sugiero que me mandes el archivo siguiendo las instrucciones que aparecen en el enlace Ayuda (en la parte superior de la plantilla).

DAPP 18 marzo, 2013 21:48  

hola jose buenos dias tengo una duda como le hago para replicar esta informacion a 1000 filas ?????

Anónimo,  31 marzo, 2013 19:23  

Hola Jorge,

En primer lugar muy bueno tu blog, lo sigo y lo utilizo mucho. En relación a tu último blog de "listas desplegables dependientes" necesitaría hacer una combinación de estas con este post de lista con condicionales. La pregunta es, cómo sería con una macro este post?. Y así poder unirlo a las otras macros?

Gracias.

Jorge Dunkelman 31 marzo, 2013 22:51  

Hola, si te refieres a esta nota, en la rutina "insertarPaises" uso un condicional para formar la lista con los países que pertenecen a continente elegido.

Anónimo,  23 agosto, 2014 00:23  

Hola, creo que esta pregunta es muy sencilla para ud. Estoy haciendo un formulario, quiero incluir un campo en el que si escogen una determinada ciudad se les despliegue una lista con las opciones correspondientes (varias, no una) a ésta. Qué debo hacer?
Ej: Si escoge en la columna A, la ciudad de NY, en la columna B se despliegan los sitios que puede visitar (Estatua de la Libertad, Times Square) pero si escoge Washington, se despliega el Pentágono, la Casa Blanca).

Gracias!

Gracias

Jorge Dunkelman 23 agosto, 2014 09:57  

En este post puedes ver una solución sin macros.
En esta otra puedes ver una solución con macros.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP