Un lector, cuyo mail he borrado por error y espero que lea esta nota en algún momento, me comentaba que no lograba crear listas dependientes cuando los valores estaban ubicados en un rango horizontal (filas) en lugar de vertical (columnas) tal como muestro en el ejemplo de esta nota.
Como en el caso anterior, el problema reside en el hecho que la función INDIRECTO sólo funciona con texto.
Cuando queremos crear una lista de valores desplegable en Excel, la opción Lista de validación de datos es la alternativa más fácil.
Veamos el caso. Los nombres de los agentes de cada zona de una empresa aparecen en esta hoja en rangos horizontales
Empezamos por crear el nombre que contiene el rango vertical de las zonas
Ahora asignamos la primer celda de la cada celda del la columna C al nombre de cada zona. Es decir
Norte = $C$3
Sur=$C$4
Este=$C$5
Oeste=$C$6
En la celda B9 de la misma hoja ponemos la lista desplegable de las zonas
En la celda B10 creamos la lista desplegable dependiente de los agentes con esta fórmula
=DESREF(INDIRECTO($B$9),0,0,1,CONTARA(INDIRECTO(FILA(INDIRECTO($B$9))&":"&FILA(INDIRECTO($B$9))))-1)
Como ven, combinamos INDIRECTO y FILA para "construir" el texto que representa el rango de los nombres de cada zona, en forma dinámica.
Esta solución tiene un grave problema: si ponemos las listas desplegables en otra hoja, la fórmula no funcionará correctamente. Aquí nos enfrentamos con dos problemas:
1 – tenemos que crear una referencia a la hoja que contiene los valores de las listas (en nuestro ejemplo, la Hoja1)
2 – en Validación de datos no podemos usar referencias a hojas remotas directamente; tenemos que "encapsularlas" en un nombre.
Por estos motivos creamos el nombre "lista_dependientes" que se refiere a esta fórmula:
=DESREF(INDIRECTO(Hoja2!$B$3),0,0,1,CONTARA(INDIRECTO("Hoja1!"&FILA(INDIRECTO(B3))&":"&FILA(INDIRECTO(B3))))-1)
En esta fórmula creamos el texto de la referencia en la función CONTARA poniendo en forma explícita el nombre de la hoja que contiene las listas de valores (Hoja1).
Ahora podemos ser fieles al principio de separar los datos de los reportes y usar la lista dependiente en la Hoja2
El archivo del 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.
Hola Jorge felicitaciones y gracias nuevamente por tu gran aporte.
ResponderBorrarTe comento que uso otra fórmula para este tipo de validaciones, ya que INDIRECTO presenta el problema de ser "volátil", es decir se recalcula para cada cambio, por lo que se torna lenta en hojas con gran cantidad de datos. Yo suelo usar para este tipo de fórmulas para listas una combinación basada en COINCIDIR, que para el caso de la celda B10 sería:
=DESREF($B$2;COINCIDIR($B$9;Zona;0);1;1;CONTARA(DESREF($B$2;COINCIDIR($B$9;Zona;0);1;1;200)))
Qué opinión te merece?
Saludos.
José de Diego.
Hola José,
ResponderBorrargracias por el aporte. Efectivamente, INDIRECTO es volátil. También DESREF es volátil, pero tu fórmula mejora mucho el tiempo de recálculo de la hoja en caso de haber muchas fórmulas.
Buenas.
ResponderBorrarMe llama mucho la atención el tema de "volátil" que habeis utilizado. ¿Qué más implica? ¿Cómo se puede saber si una función és volátil o no? Y si utilizas excels muy grandes, a parte de fórmulas volátiles, que otras cosas se puede tener en cuenta para ser más eficiente con los cálculos
Saludos
El tema está relacionado con el sistema que usa Excel para calcular las fórmulas en las hojas. Tema importante, veré de publicar algo en los próximos días. Mientras tanto, se puede consultar esta nota (en inglés) en el excelente sitio de Charles Williams.
ResponderBorrarPara darte una respuesta tendría que ver tu cuaderno. Puedes mandarme lo a la dirección que aparece en el enlace Ayuda.
ResponderBorrarHola Jorge,
ResponderBorrarEs la primera vez que escribo. Enhorabuena por el blog. Es muy bueno. Yo te voy a preguntar por una variación de este ejemplo, un poco más sencilla, pero que aún así no se hacer.
Supongamos que sólo tenemos cuatro agentes, uno por cada zona. ¿Cómo tendría que hacer para que si yo eligiera una zona me saliera automáticamente en otra casilla el agente asociado a esa zona?
Saludos
Ric,
ResponderBorrarcon la función BUSCARV, usadno el resultado de la lista como argumento "valor de búsqueda" de la función.
Gracias. Ya lo he conseguido. Ahora me surge una duda sobre validación de datos: Al hacer un desplegable de esta forma y elegir "Lista" como criterio de validación no me deja elegir una lista desde otra pestaña u hoja de excel. Esto cómo sería posible de hacer?
ResponderBorrarGracias.
Un saludo
Ric,
ResponderBorrarpara usar un rango ubicado en otra hoja o cuaderno, hay que usar un nombre que se refiera a ese rango.
Hola, hace unos pocos días que estoy aprendiendo un montón gracias a tu web, gracias porque es claro e instructivo.
ResponderBorrarMi duda es la siguiente:
Tengo varias celdas con validacion por lista de datos, todo correcto. Mi intención es una vez todo marcado, pasar todos estos datos a una hoja aparte y que se quede guardado (a modo de Factura por así decirlo), se que se puede hacer pero lo cierto es que no encuentro el modo.
Te agracezco tu aporte.
Te dejo mi correo y si lo ves necesario te mando el libro, ramon.conejos@gmail.com
Gracias de nuevo por tus aportaciones
Creo que la técnica que muestro en esta nota te puede ser útil
ResponderBorrarAgregar datos en la primer fila libre de la tabla
Tal vez más adelante publique algo más elaborado.
Claudio,
ResponderBorrarhay varias formas de hacerlo, pero la explicación es demasiada larga para el marco de un comentario (irá nota sobre le tema). Mientras tanto, podrías crear una tabla dinámica filtrándola para que muestre sólo los nombres del estátus TRABAJA. Luego crear una rango dinámico que se refiera al rango de los nombres que aparecen en esa tabla dinámica.+Hay otras soluciones con fórmulas y, por suspuesto, con macros.
Lo prometido es deuda, aquí está la nota.
ResponderBorrarEstimado:
ResponderBorrarTu ejemplo esta clarisimo, pero quisiera arrastrar la formula de zona y agente para que se copie en otras celdad y me bota datos erroneos
Enviame un mail con la descripción de lo que intentas hacer y el problema (incluyendo el cuaderno). La dirección aparece en el enlace Ayuda, en la parte superior de la plantilla.
ResponderBorrarHola, tengo un problema, parece que la solución es sencilla pero no la encuentro. Esto haciendo una base de datos de accidentes y tengo una columna de "CAUSA GENERAL" y otra columna dependiente de esta llamada "CAUSA ESPECÍFICA). Al poner en la primera celda de causa específica =INDIRECTO(COLUMNA.FILA) todo sale bien. El problema me surge cuando en la siguiente fila de causa específica (segundo accidente), la lista no me aparece, pues la celda tiene la ubicación de la celda superior. Cómo hago para hacer en un solo paso la configuración de todas las celdas de la columna CAUSA ESPECÍFICA dependientes del valor de la celda correspondiente en la columna CAUSA GENERAL? Espero puedan ayudarme, gracias.
ResponderBorrarHola, fijate en las instrucciones que pongo en el enlace Ayuda (en la parte superior de la plantilla) y enviame el archivo.
ResponderBorrarHola, Jorge.
ResponderBorrarTrataré de ser conciso desde mi pregunta y si te fuera necesario, explícito con mi caso:
¿Hay alguna limitante o conflicto entre el uso de la función INDIRECTO y los nombres de rangos dinámicos, para listas dependientes?
Me explico:
Intento usar listas desplegables dependientes pero basadas todas ellas en nombres creados de listas o rangos dinámicos (usando necesariamente igual que en tu ejemplo la función DESREF) y luego usar estas en hoja distinta a la de los datos referidos.
Creados los nombres de este tipo de rangos dinámicos, para mi lo ideal sería simplificar todo, usando para la lista dependiente (en el cuadro Validación de Datos / Origen), un simple INDIRECTO referido a la celda de la primer lista, para que devuelva el NOMBRE de cualquiera de las listas y con ello se desplieguen... pero pareciera imposible crear algo con esas tres características (desplegables, dependientes y en hojas distintas) usando INDIRECTO.
¿Por qué sí es posible todo ello cuando la lista desplegable dependiente refiere un nombre de un rango o celda estático? como lo fue en el segundo paso de tu ejemplo.
Por tu tiempo y deseo por compartir tus conocimientos, mil gracias!
Jaime
Hola Jaime,
ResponderBorrarcomo pongo en la nota, INDIRECTO sólo acepta referencias a rangos en forma de texto. Aún cuando usamos un nombre definido como argumento, si éste se refiere a una fórmula, INDIRECTO generará un error.
En la nota combino FILA con INDIRECTO para crear una referncia en forma de texto. En esta nota muestro una solución usando la funcionalidad Tablas (Excel 2007-2013) o Listas (Excel 2003).