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.
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
ResponderBorrar"$C$2:$C$15".
Solo un error de tipeo.
Gracias por la info. saludos.
José,
ResponderBorrartenés razón. Gracias por la observación. Acabo de corregir los errores.
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.
ResponderBorrarGracias
Usando la función HIPERVINCULO.
ResponderBorrarEstimado 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?
ResponderBorrarSaludos,
Carlos
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.
ResponderBorrarGracias Gustavo. Mandame el enlace a tu web por mail privado (aparece en el enlace Ayuda). Me gustará ver lo que publicas.
ResponderBorrarCarlos, 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.
ResponderBorrarPara usarla hay que abrir el nuevo cuaderno con Nuevo (Ctrl+N), con con Abrir (Ctrl+A)
Mi solución para este problema pasa por la utilización de offsets... me parece una solución mas sencilla
ResponderBorrarAun así buena solución
Hola
ResponderBorrarMuy 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
Carlos, te sugiero que me mandes el archivo siguiendo las instrucciones que aparecen en el enlace Ayuda (en la parte superior de la plantilla).
ResponderBorrarhola jose buenos dias tengo una duda como le hago para replicar esta informacion a 1000 filas ?????
ResponderBorrarHola Jorge,
ResponderBorrarEn 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.
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.
ResponderBorrarHola, 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?
ResponderBorrarEj: 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
En este post puedes ver una solución sin macros.
ResponderBorrarEn esta otra puedes ver una solución con macros.
La función INDIRECTO convierte textos en rangos. Si sigues ese principio tendrías que incluir una condición para que se refiera a un rango (Activos) u otro (Inactivos).
ResponderBorrarSi los valores Activo e Inactivo están en la misma columna no podrías usar INDIRECTO a menos que hagas ago similar a lo que muestro en la nota. En ese caso creo que es más sencillo usar la técnica mostrada en el post.
buenos dias jorge,
ResponderBorrarquiero vincular datos a un desplegable y no logro hacer, es decir:
estoy haciendo hojas de escandallos y he hecho un desplegable en el que me aparecen los ingredientes, pero quiero que al seleccionar por ejemplo el aguacate, en las columnas de al lado automáticamente me aparezca;
-unidad de medida (KG)
-precio por kilo
-y merma
datos que tengo evidentemente en otra hoja de calculo
muchas gracias
Bien, para eso tendrás que usar alguna función como BUSCARV o INDICE para obtener los datos de la hoja remota.
ResponderBorrar