En esta nota veremos un rodeo sencillo a este problema, sin usar macros o funciones definidas por el usuario.
Por ejemplo, si queremos construir una lista desplegable que muestre las sucursales de red
Para crear la lista desplegable usamos validación de datos, con la opción Lista donde usamos como referencia el nombre Sucursales que contiene el rango D2:D13
La referencia al rango en el nombre es absoluta, por lo que si agregamos sucursales a continuación del último valor de la lista, deberemos editar el nombre y cambar la referencia.
Para lograr que la lista se actualice automáticamente al agregar nuevas sucursales tenemos que definir el rango como rango dinámico. Para esto usamos la función DESREF
=DESREF(Hoja2!$D$2,0,0,CONTARA(Hoja2!$D:$D),1)
Aquí pueden leer una explicación detallada sobre la función DESREF (OFFSET en la versión inglesa).
Ahora vamos a agrupar las sucursales por zonas
La idea es elegir una zona en B3 y que la lista desplegable en B4 muestre sólo las sucursales correspondientes. Para esto usaremos validación de datos con la opción lista y en Origen pondremos INDIRECTO(B3). Esto funciona bien si usamos referencias absolutas. Por ejemplo, definimos el nombre “absOeste” como $H$3:$H$5
Nótese que la fórmula en Origen es =INDIRECTO(“abs”&B3), es decir concatenamos el nombre de la zona en B3 con “abs” para obtener el nombre “absOeste” que le hemos puesto al rango.
Si agregamos la sucursal 13 en H6, ésta no queda incluida en el rango del nombre. Podemos usar la fórmula “tradicional” con DESREF para crear el nombre “dinOeste”
Al tratar de crear la lista desplegable con validación de datos recibimos esta advertencia
Como explicamos más arriba, INDIRECTO no puede evaluar fórmulas, sólo texto. En lugar de DESREF o fórmulas definidas por el usuario (macros) como sugieren algunos sitios y foros, podemos usar la funcionalidad Tablas en (Listas Excel Clásico).
Veamos el proceso, primero en Excel 2007 y luego en Excel Clásico.
En B2 creamos una lista desplegable con validación de datos poniendo los nombres de las zonas directamente en la ventanilla Origen
Para crear el rango dinámico Norte seleccionamos las celdas E2:E5, activamos la pestaña Insertar y pulsamos Tabla. Marcamos la opción “La tabla tiene encabezados” y pulsamos Aceptar
Seguidamente activamos Herramientas de Tablas y en Nombre de la tabla cambiamos el nombre por defecto por Norte
Repetimos el mismo proceso para las restantes tres zonas. Ahora en B4 ponemos una lista desplegable con Validación de Datos-Lista y la fórmula =INDIRECTO($B$3)
Las tablas se expanden automáticamente, por lo que al agregar la sucursal 13 en la zona Oeste, ésta aparecerá en la lista desplegable.
En Excel Clásico (versiones 97-2003) usamos la misma técnica pero con algunas diferencias.
En lugar de Tablas, la funcionalidad en Excel Clásico es Listas. Para convertir un rango en una lista usamos el menú Datos-Lista-Crear Listas.
En Excel Clásico no tenemos la posibilidad de darle un nombre a la lista, por lo que usaremos el menú Insertar-Nombre-Definir
Seleccionamos el rango F4:F6 y creamos el nombre Norte. De la misma manera creamos las listas y los nombres del resto de las zonas. Al agregar nuevas sucursales en las zonas, la lista se expande automáticamente
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.
Julio, como siempre, excelente explicación. Feliz Año Nuevo.
ResponderBorrarGran aporte. Se ve que no descansas. TE deseo un Feliz Año.
ResponderBorrarGracias Anotnio 8688, pero por favor anotá: el autor de las notas es Jorge, no Julio. Ya lo aclaré en tu anterior comentario.
ResponderBorrarGracias, necesitaba saber cómo hacer ésto.
ResponderBorrar¿Sería posible poder descargar el archivo empleado como ejemplo?
Gracias de nuevo.
No guardé el archivo. Lo reconstruiré y pondré un enlace para desacargarlo, en breve.
ResponderBorrarHola Jorge!
ResponderBorrarTengo una consulta sobre esta nota, no sé si lo que quiero se puede hacer, pero si alguien puede ayudarme sos vos.
En la validación de la celda B3 pusiste directamente los textos: Norte, Sur, Este y Oeste. Se podría hacer con los nombres de rangos o nombres tablas en 2007?, porque de esta manera si hubiera algún cambio en los nombres de rangos la validación se ajustaría automáticamente, y si está hecha con textos habría que editar la validación de la celda B2 y de todas las celdas donde se haya utilizado, que en mi caso, sería en varios lugares distintos en distintas hojas del documento.
Te agradezco mucho por hacer este blog, que es realmente muy útil, y por tu ayuda.
Un saludo
María José,
ResponderBorrarsi, se puede hacer. Tanto con Excel 2007 como con Excel clásico.
Y cómo se hace? Me puedes ayudar?
ResponderBorrarAsí como hemos definido los nombres para las sucursales, defines una lista/tabla para las zonas.
ResponderBorrarEsa opción la sé hacer, pero me obliga a tener otra tabla auxiliar.
ResponderBorrarMi duda es si hay alguna manera de escribir directamente en el campo Origen de la validación, a través de alguna función, los nombres de rango.
Muchas gracias por tu ayuda.
No, o pones la lista de valores en Origen o una referencia a un rango que contiene los valores. Si se trata de un rango dinámico, la única opción es una referencia al rango. No hay una función en Excel que pueda crear valores, fuera de ALEATORIO, por supuesto.
ResponderBorrarAlberto,
ResponderBorrarHola Jorge y feliz año ante todo; una pregunta: para qué vale exactamente lo de "Lista" (la que aparece el asterisco azul al final de la tabla) y qué diferencia hay entre eso y por ejemplo autofiltro (Datos->Filtro->Autofiltro).
Saludos.
Me lo imaginaba, pero tenía la esperanza de que hubiera algún truquito con alguna función.
ResponderBorrarMuchas gracias y hasta la próxima!!!
Alberto,
ResponderBorrarlas listas (en excel 2003) o tablas (Excel 2007) es una forma de organizar los datos a la que Excel da ciertas funcionalidades. Por ejemplo, que el rango de la tabla se expande automáticamente a medida que agregamos (o se contrae si quitamos) datos. Otra característica es que si en una columna de la tabla hay una fórmula, al agregar una fila la fórmula es copiada automáticamente en la celda correspondiente. Es tal vez una de las funcionalidades más subestimadas en Excel. En breve estaré publicando una nota sobre el tema.
Autofiltro es una funcionalidad que nos permite ocultar filas enteras basándonos en algún criterio de alguna o algunas de las celdas en una columna.
Hola:
ResponderBorrarHace ya tiempo que intento hacerme un libro de contabilidad para casa y esta información me ha venido muy bien, pues me permite incluir cun concepto global (Banco, p.e.) y luego, en función de ese concepto, que se abra una segunda lista específica.
Sin embargo mi problema surge cuando la función INDIRECTO ha de evaluar un texto con espacios por medio (Gastos varios, p.e.). En este caso no encuentro el modo de que se abra esta segunda tabla. Bueno, el modo que se me ocurre es introducir el concepto glogal con "_" (Gastos_varios, p.e.) pero estéticamente no me convence.
Quizás mi duda sea una tontería pero no consigo solucionarlo.
Muchas gracias
La solucion es combinar la funcion SUBSTITUIR con la funcion INDIRECTO. Fijate en el ejemplo que muestro en esta nota.
ResponderBorrarMuchas gracias.
ResponderBorrarSoy nuevo en esto y no sabía cómo solucionarlo. Mayor rapidez y eficacia...imposible.
Gracias!
ResponderBorrarEsta solución si que esta muy buena y muy facil de hacer muchas gracias por descubrirnos estas herramientas que tiene excel que no sabia utilizar
ResponderBorrarBuenas tardes,
ResponderBorrarllevo todo el día leyendo tu blog. Mi herramienta de trabajo es Excel.
Algunas de las técnicas ya las conocía pero la mayoría no, sobretodo de tu blog de graficos.
Por todo ello, gracias, gracias, gracias y buen trabajo!
Un cordial saludo!
Que buena explicacion,Jorge tan claro como siempre.
ResponderBorrarPavilla79
Primero, Gracias por el blog, ha sido de mucha inspiración.
ResponderBorrarSegundo, si elimino un elemento de la lista me aparecen los espacios en blanco en la validacion. ¿Puede solucionarse este problema?
Gracias
Si el rango dinámico está definido en forma correcta no deberían aparecer espacios en blanco. ¿Podrías ser más específico?
ResponderBorrarBuenas Noches Jorge,
ResponderBorrarTengo una duda con los rangos dinámicos nominados (para las listas desplegables).
Hay alguna técnica para nominar (nombrar) que sea rápida?
Tengo que un cuadro de la distribución de ventas de 4 productos, para 4 paises y cada uno tiene direcciones, gerencias, zonas, distribuidores y puntos de venta. (5 niveles ciales.)
Además hay cambios con relativa frecuencia.
Se puede hacer algo para evitar crear y mantener los 160 columnas con las listas de nombres?
Conoces alguna técnica para que no sea tan pesado?
Muchas gracias y enhorabuena por el blog.
eres un monstruo del excel ;)
Saludos desde España,
José María
La única forma que conozco de crear varios nombres de un golpe es seleccionar los rangos y activar "Crear desde la selección".
ResponderBorrarAhora, esto crea rangos fijos, no dinámicos. Así que cada vez que se produce un cambio, tendría que redifinir el nombre.
que cantidad de nombres de rango se pueden ingresar ya que intento poner una validacion en base a un codigo postal pero serian una cantidad exagerada de tablas! o hay alguna otra manera
ResponderBorrar¿Podrías explicar un poco más la consulta? No me queda claro si se trata de poner una validación de datos en muchas celdas o crear muchas listas de vallidación.
ResponderBorrar... y cómo puedo utilizar un nombre definido en una macro, que no hace referencia a un rango sino que devuelve un valor (ya que es una fórmula). Me explico: creo un nombre "MiValor" que es una fórmula compleja, con funciones anidadas, y quiero en mi macro referirme a élla, algo asi como: If MiValor > x/y Then ...
ResponderBorrarEn la macro no podría decir Range("MiValor") ya que MiValor no es un rango.
Gracias por la ayuda que me puedas ofrecer.
Puedes usar la sentencia
ResponderBorrarEvaluate("MiValor")
o la forma abreviada
[MiValor]
Muchas gracias por el artículo me ha sido de gran ayuda en otras ocasiones, El día de hoy he complicado el asunto un poco, en mi desarrollo tengo una tabla que alimento de una base de datos con la información de los diferentes combos, esto con el fin tener una administración más sencilla de los datos, el problema es que al usar el indirecto (TablaParametricas[Estado]) me genera un error “Error de formula”
ResponderBorrarEs importante aclarar que la tabla donde tengo los datos se llama TablaParametricas y una de las columnas de la tabla es “estado” el objetivo es que el combo se llene con esta información
Agradezco si me puedes dar una ayuda al respecto.
Eduardo,
ResponderBorrartienes que usar (TablaParametricas[Estado]) sin la función INDIRECTO.
Hola Jorge! Gracias por el artículo.
ResponderBorrarTengo una consulta:
¿Es posible usar la función INDIRECTO() asociado a un rango discontinuo?
Me explico: Si asigno el nombre "hola" al rango continuo A1:A4, al usar INDIRECTO(hola), me estaré refiriendo al rango A1:A4. Así al usar SUMA(hola), me sumará el contenido de las celdas A1, A2, A3 y A4.
Ahora, si en la celda B1 escribo "hola", creo un nuevo nombre en forma manual, por ejemplo "variable" que se refiera a =INDIRECTO($B$1), al hacer la función SUMA(variable), también me arrojará la suma del contenido de las celdas A1, A2, A3 y A4.
¿Pero qué pasa si mi rango es discontínuo?
Por ejemplo, si mi rango definido por A1, A3, A5, A7, A9 y le asigno el nombre "miRango", al usar la función SUMA(miRango), me suma las celdas A1, A3, A5, A7, A9 sin problemas. Pero si creo un nuevo nombre "variable1" =INDIRECTO($B$2), donde en B2 puse "miRango", al ocupar la SUMA(variable1), me arroja el error #¡REF!.
¿La función INDIRECTO no soporta rangos discontínuos?
¿Hay alguna manera de hacerlo?
Muchas gracias!!
Estaré atento a tu respuesta.
Saludos,
Mauricio.
Así es, INDIRECTO no soporta rangos discontinuos, es más, los odia, no los aguanta, los detesta :)
ResponderBorrarY ahora seriamente, no se pueden usar rangos discontinuos con INDIRECTO (y también con otras muchas funciones de Excel).
Un rodeo posible sería crear un rango continuo creando referencias a las celdas del rango discontinuo, En tu caso podría ser, por ejemplo
C1=A1
C2=A3
C3=A5
C4=A7
C5=A9
y definir el nombre miRango que se refiera al rango C1:C5
Muchas gracias por tu respuesta Jorge!
ResponderBorrarTendré que hacero así entonces.
Saludos,
Mauricio.
Gracias por este post. Pude resolver justo lo que necesitaba.
ResponderBorrarEstimado, quiero hacer algo parecido a esto pero no exactamente esto, llevo varios dias buscando e intentando con indirecto, con buscarv, con desref, pero no me resuta, y aunque seguiré intentando, acudo a solicitarte consejo.
ResponderBorrarte explico para ver si por favor me puedes ayudar:
- tengo una hoja con una tabla en la que registro el estado actual de varias caracteristicas a evaluar en diferentes sucursales.
- las caracteristicas a evaluar van hacia abajo en la columna A y las sucursales hacia la derecha en la fila 1.
- el estado de cada caracteristica lo voy ingresando manualmente para cada sucursal.
lo que necesito hacer es lo siguiente:
- en otra hoja tener un consolidado solamente con 2 filas: la de las caracteristicas a evaluar y la de la sucursal que yo elija en un menu desplegable. este menu ya lo hice y funciona bien, lo que no he podido hacer es que cada vez que yo cambie en el menu a una sucursal distinta, las celdas inferiores cambien todas, trayendo el texto que les corresponde en la tabla grande de la hoja 1.
¿se entiende?
espero que si y sino estare muy atento a alguna respuesta tuya.
Saludos y muchas gracias
Me parece que podrías hacerlo con una tabla dinámica, pero tendría que ver tu modelo para entederlo cabalmente. Te sugiero que te pongas en contacto conmigo por mail privado (fijate en el enlace Ayuda, en la parte superior del blog).
ResponderBorrarexcelente tutorial , me gustaría ver el ejemplo pero esta el link caido
ResponderBorrarAcabo de probar el enlace y veo que funciona. Si no logras descargar el ejemplo contactame por mail privado y te enviare el cuaderno.
Borrar