Listas desplegables dependientes múltiples

lunes, agosto 04, 2008

En la nota anterior sobre listas desplegables dependientes mostramos cómo crearlas con validación de datos.
En esa nota vimos como crear una lista desplegable de países y ciudades. Una vez elegido el país, la segunda lista desplegable mostraba sólo ciudades de ese país.
Algunos lectores me consultan como hacer lo mismo pero con más de dos niveles de dependencia. Por ejemplo, continentes-países-ciudades.
La técnica es básicamente la misma. Incluimos las listas en rangos nominados (dentro de nombres, usando Insertar-Nombres-Definir) y luego usamos validación de datos con la opción Lista y en Origen usamos fórmulas con la función INDIRECTO.
Supongamos este cuaderno con cuatro hojas



En la hoja Continentes tenemos una lista de los continentes

listas desplegables dependientes

Estos valores nos servirán como referencia a los nombres que contendrán la lista de países de cada continente. Definimos el rango A1:A7 dentro del nombre "continente"

continente=Continentes!$A$1:$A$7

En la hoja Países creamos campos con las listas de los países de cada continente. Por comodidad (la mía) he puesto sólo dos países por continente



Finalmente ponemos listas de las ciudades por países en la hoja Ciudades

listas desplegables dependientes


Ahora tenemos que definir los nombres. Una forma de ahorrar tiempo es usar el método Insertar-Nombre-Crear. En la hoja Países seleccionamos la celda A1 y abrimos el menú Insertar-Nombre-Crear



Como queremos que Excel use la fila superior como rótulo para los nombres, señalamos la opción "crear nombres en fila superior".

El resultado será:

África=Paises!$A$2:$A$3
América_del_Norte=Paises!$B$2:$B$3
América_Central=Paises!$C$2:$C$3
América_del_Sur=Paises!$D$2:$D$3
Asia=Paises!$E$2:$E$3
Europa =Paises!$F$2:$F$3
Oceanía=Paises!$G$2:$G$3
Como verán Excel ha agregado "_" en los casos que el nombre del continente está formado por más de una palabra. Esto se debe a que por definición los nombres en Excel no pueden tener espacios en blanco (ni símbolos especiales).

Usamos el mismo método para definir los nombres en la hoja Ciudades, con este resultado

Congo =Ciudades!$A$2:$A$61
Canadá =Ciudades!$B$2:$B$61
México=Ciudades!$C$2:$C$61
Venezuela=Ciudades!$D$2:$D$61
China=Ciudades!$E$2:$E$61
Alemania=Ciudades!$F$2:$F$61
Australia=Ciudades!$G$2:$G$61
Angola =Ciudades!$H$2:$H$61
Estados_Unidos=Ciudades!$I$2:$I$61
Guatemala=Ciudades!$J$2:$J$61
Colombia=Ciudades!$K$2:$K$61
Vietnam=Ciudades!$L$2:$L$61
Francia =Ciudades!$M$2:$M$61
Islas_Fidji=Ciudades!$N$2:$N$61

Como ven, todos los nombres tiene el mismo tamaño de rango (de la fila 2 a la 61), lo que hará que en la lista desplegable aparezcan espacios en blanco. Más adelante veremos como solucionar este problema.

Ahora que hemos definido todos los nombres, definimos las listas desplegables en la hoja "Elección"

En la celda B1 definimos la lista con la opción Lista y la fórmula "=continente". Esto crea una referencia al rango que contiene los nombres de los continentes

listas desplegables dependientes

En la celda B2 creamos la lista de países que será dependiente del continente elegido en la celda B1



En este caso creamos la referencia al rango usando la función INDIRECTO. Además tenemos que usar la función SUSTITUIR para poner las líneas "_" en lugar de los espacios entre las palabras, para que el valor de la celda coincida con el nombre del rango

=INDIRECTO(SUSTITUIR(B1," ","_"))

Usamos la misma técnica en la celda B3, usando como referencia el valor de la celda B2

listas desplegables dependientes

Si elegimos el continente América del Sur, podremos elegir sólo Venezuela o Colombia. Si elegimos Colombia podremos elegir una de las ciudades que hemos incluido en la columna Colombia de la hoja Ciudades



Si fuera necesario podríamos agregar más listas dependientes creando los campos adecuados (barrios, jurisdicciones, etc.).

La técnica que hemos mostrado adolece de un defecto estético, los espacios en blanco. Para solucionar este problema tenemos que usar rangos dinámicos. Normalmente usamos la función DESREF para crear rangos dinámicos. El problema en nuestro caso es que la función INDIRECTO solo acepta rangos como argumento.
Para superar este problema usaremos esta fórmula en la creación de la lista desplegable dependiente de la celda B3 (ciudades)

=DESREF(INDIRECTO(SUSTITUIR(B2," ","_")),0,0,CONTARA(INDIRECTO(SUSTITUIR(B2," ","_"))),1)

listas desplegables dependientes

Ahora la lista dependientes de ciudades no mostrará espacios en blanco.



La fórmula fue tomada del excelente sitio Contextures de Debra Dalgleish.

El cuaderno con el ejemplo y las fórmulas puede descargarse 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.

95 comments:

JULITO 05 agosto, 2008 17:53  

Estimado señor Jorge, puede apoyarme en decir como puedo descargar la hoja en excel por que cuando quiero ver el cuadernillo de hoja me lleva al pdf.

Gracias

Jorge L. Dunkelman 06 agosto, 2008 00:06  

Hola

después de pulsar el enlace en la nota, en la página que se abre, abajo a la derecha verás un enlace con la palabra "Download". Tienes que pulsar ese enlace para hacer la descarga.

Marco Paez 07 agosto, 2008 16:06  

Estimado Jorge Buenos Días:
Felidades por tu aportacion al buen manejo de Excel. viendo la ultima publicacion en tu BLOG de listas despegables multiples , he bajado el archivo para su analisis y aplicar el metodo en un archivo algo similar dinde tengo 31 estados y cada estado tiene su clave , asi como sus minicipios y cada municipio tambien le corresposponde una clave , ejemplo
Estado Sonora clave 26, el estado tiene 72 municipios y a cada municipio le corresponde una clave de indentidad , al aplicar el metodo de Ahora tenemos que definir los nombres. Una forma de ahorrar tiempo es usar el método Insertar-Nombre-Crear. En la hoja Países seleccionamos la celda A1 y abrimos el menú Insertar-Nombre-Crear en fila superior no funciona me manda un mensaje de advertencia.
que no me deja realizar dicho paso..
me podrias orientar ya que no he encontrado la solucion al mismo .
gracias por tu ayuda

Jorge L. Dunkelman 08 agosto, 2008 18:07  

Hola Marco

qué dice el mensaje? Puedes mandarme el archivo?

Anónimo,  13 agosto, 2008 14:57  

FELICIDADES PROFESOR,MUY BUEN EJEMPLO,LO FELICITO

Jhonyac2 05 diciembre, 2008 09:25  

Hola Jorge, muy bueno su blog. Como seria la implementación de este mismo caso pero en un formulario, donde las listas las obtengo a traves de un combobox.
Muchas gracias por la ayuda.

Jorge L. Dunkelman 07 diciembre, 2008 19:23  

Tienes que programar los controles con Vba, lo cual va más allá de lo que se puede exponer en un comentario. En cuanto tenga un poco de tiempo publicaré una nota sobre el tema.

Jhonyac2 10 diciembre, 2008 07:06  

Muchas gracias Jorge, estaré para cuando puedas publicar la nota.

Debol 13 abril, 2009 01:23  

Respecto al defecto estético corregido con offset y counta, cómo hacer para que se eliminen los espacios en blanco cuando éstos se encuentran intercalados entre el rango? Estoy intentando varias opciones, pero o no me salen o son muy rudimentarias.

Muchísimas gracias. El blog es espectacular.

Debol 14 abril, 2009 15:35  

Por supuesto que me sirve Jorge. Grande ese "SMALL". Lo utilizaré bastante a partir de ahora. Mil gracias.

Carlos

Anónimo,  01 mayo, 2009 14:58  

Hola Jorge.
Hace unos días te hice una consulta en el hilo "Listas desplegables dependientes en Excel con Validación de Datos."
a la cual me respondiste enviandome a este hilo. La pega me surge al intentar crear las listas desplegables a partir de la siguiente matriz, en la que hay valores que se repiten.

Persona Talla Zapato Calcetín
Pedro 41 Rojo
Pedro 43 Verde
Luis 42 Azul
Jose 44 Rojo
Carlos 41 Amarillo

¿como puedo solucionar este problema?.

Un saludo y muchas gracias.

Luis.

Jorge L. Dunkelman 01 mayo, 2009 20:59  

Para crear una lista desplegable sin duplicados puedes usar la técnica que muestro en esta nota.
También puedes crear una lita de valores únicos usando Filtro Avanzado.

tassy 13 mayo, 2009 18:25  

hola, mi pregunta es sencilla creo yo, necesito utilizar esto de las listas desplegables dependientes para facilitar la captura de varias filas que funcionan como registros, el problema con el metodo que usted expone es que solo funciona en una celda determinada, existe alguna manera de que la regla de validacion reconozca por ejemplo que B2 depende de A2 y B3 del valor que hay en A3 y asi sucesivamente sin tener que cambiar celda por celda la regla de validacion? es decir como un comodin que me diga de esta fila la columna A o algo por el estilo? si no que me recomienda utilizar vba, macros?

Jorge L. Dunkelman 19 mayo, 2009 21:28  

Tendrías que crear una regla para celda. Es decir un nombre que represente los valores depedendientes de el valor de la celda de origen.

Robert Blanco 25 septiembre, 2009 16:00  

Una consulta sobre el tema: Como puede hacerse para que cada vez que se elija un nuevo continente, se refresque las celdas de pais y cuidad de tal manera que las mismas aparezcan vacias?. Gracias por su ayuda

Jorge L. Dunkelman 25 septiembre, 2009 17:56  

En el comentario del 20 de junio 2007 en la nota sobre listas desplegables dependientes puedes leer:


Hola,
lo único que se me ocurre es usar un evento (macro) ligado a la hoja.
Para nuestro ejemplo:
1 - en la pestaña Hoja1 haces clic con el botón derecho del mouse y abres Ver Código
2 - en el módulo de la hoja pones este código

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A5"), Target).Address = Range("A5").Address Then
Range("B5").ClearContents
End If
End Sub

Lo que hace este evento es que cada vez que hay un cambio en el valor de A5 (el país) el contenido de la celda B5 es borrado.

Anónimo,  27 octubre, 2009 13:10  

Hola ;

Soy josemaria, me gustaria hacerte una pregunta que he estado buscando en muchos sitios y por lo que he leido creo que no tiene solución pero sigo en mi empeño de que creo que puede ternerlo aunque yo no sepa.


Tengo una lista despleglable con muchos valores, (sobre 15), pero al tener tantos y el nombre de cada uno de esos valores alrededor de 15 caracteres, aparece muy pequeño.

Mi pregunta es , si se le puede dar formato de alguna forma a la lista desplegable para que salga mas grande.

¿Existe pa posibilidad?


Un Saludo.
Jose Maria.

Jorge L. Dunkelman 27 octubre, 2009 20:48  

José María

Si estás creando la lista con Validación de Datos, no podés cambiar el tamaño de la fuente. Pero basta con cambiar el ancho de la columna para poder ver el contenido de la lista (el tamaño de la fuente no cambia).
Si creaste la lista con un control ActiveX podés camniar el tamaño de la fuente en la ventana de propiedades del control.

Anónimo,  02 diciembre, 2009 17:19  

Gracias Jorge por tu blog y tus conocimientos!
Queria consultarte: existe alguna manera "sencilla" (es decir mas sencilla) de hacer que excel busque la columna sin tener que definir cada nombre y su columna.
O sea evitar esto:
Congo =Ciudades!$A$2:$A$61
Canadá =Ciudades!$B$2:$B$61
México=Ciudades!$C$2:$C$61
Venezuela=Ciudades!$D$2:$D$61
China=Ciudades!$E$2:$E$61
Alemania=Ciudades!$F$2:$F$61
Australia=Ciudades!$G$2:$G$61
Angola =Ciudades!$H$2:$H$61
etc...
tal vez la funcion coincidir...

Jorge L. Dunkelman 02 diciembre, 2009 18:10  

Toda la idea es que la lista desplegable funcione en base a una elección del usuario (el usuario elige "Congo" y el rango indicado se carga en la validación de datos). Por eso no veo cuál técnica pueda ser más sencilla que esa. Por ejemplo, si el usuario quiera elegir una de las ciudades de Congo, cómo haría para informarle a Excel que despliegue esa lista?
Ahora, podríamos evitar tener que poner encabezamiento al rango que contiene la lista usando alguna función como COINCIDIR, pero no veo cuál seía la ventaja. La fórmula que definiría el rango sería mucho más complicada.

Anónimo,  25 agosto, 2010 07:01  

Hola Jorge, tu blog es muy últil por adelantado te agradesco tu respuesta, te comento mi problema:
Tengo 2 hojas en una tengo mis objetos con el nombre de cada rango y la otra tengo filas de registro donde debo indicar el servicio brindado y por servicio a que cliente se atendió. Hago el mismo procedimiento que indicas pero el problema es que mi columna de clientes siempre me filtra según mi primera celda de filtro de servicios, cuadno deberia filtrarme según su servicio que haya escogido en esa fila. Espero me entiendas, puedes ayudarme por favor. Gracias ... muchisimas gracias!

Jorge L. Dunkelman 25 agosto, 2010 07:55  

Necesitaría ver el archivo. Ponete en contacto conmigo por mail privado.

Anónimo,  25 agosto, 2010 17:50  

Jorge gracias por tu respuesta, acabo de enviarte un correo con mi archivo. mi nombre es Susan
Gracias!

CY 11 enero, 2011 18:13  

Hola Jorge,

Gracias por los tips. Tengo un problema relacionado con este ejemplo, el cual no se como enfrentar.

A diferencia de lo que presentas, poseo una tabla con datos (bastante grande, de 250 registros) que ya posee continente;pais;ciudad;distrito;etc

Me gustaría saber cómo podría (o que recomiendas) para generar la tabla (u hojas, según tu ejemplo) de manera rápida, para poder entonces aplicar la lista desplegable dependiente.

Atento a tu respuesta y de antemano gracias

Slds

kuidro@gmail.com

Jorge L. Dunkelman 12 enero, 2011 07:19  

Hola,
si se trata de crear tres o cuatro hojas, como en mi ejemplo, no veo el problema de hacerlo manualmente y pasar los datos a las hojas con Copiar/Pegar.
Por otro lado, las listas no deben estar necesariamente en distintas hojas, basta que estén en ragos separados.

Anónimo,  23 enero, 2011 21:45  

Estimado amigo.. muy bueno tus aportes.. pero tengo un problema.. no me basta con sustituir los espacios por sub guion "_".. sino que ademas necesito cambiar "." por /... especificamente esta es la palabra que necesito

TUB AF JP 6M 1/2

significa: tuberia agua fria junta para pegar de 6m de 1/2 pulgada...

Saludos

rmillan200x@gmail.com

Jorge L. Dunkelman 24 enero, 2011 07:14  

Tienes que combinar dos funciones SUSTITUIR.Suponiendo que el valor está en la celda A1:

=SUSTITUIR(SUSTITUIR(A1," ","_"),"/",".")

Luis Ramirez Piedra 09 febrero, 2011 01:31  

Estimado Jorge, de antemano te agradezco tu ayuda.
Cómo puedo ligar con listas dependientes los siguientes datos:

- Perpectivas
- Objetivos tácticos
- Iniciativas estratégicas

Las preguntas específicas son las siguientes:

1. Cómo puedo ligar los valores de los objetivos tácticos con la perspectiva correspondiente?
2. Cómo puedo ligar los valores de las iniciativas estratégicas con los objetivos tácticos correspondientes?
3. La idea es que la iniciativa estratégica depende de su correspondiente objetivo y, éste a su vez, de su correspondiente Perspectiva.
4. Si además, deseo mostrar las listas desplegables dependientes en la hoja:Resultados

A qué dirección de correo te puedo enviar el archivo con los datos correspondientes. Te comento que ya tengo los nombres definidos, sin embargo, he estado probando, pero hay algún paso en que estoy cometiendo un error, a la hora de ligar la 2da lista con la primera, además, cómo ligar la 3ra con la segunda.

De antemano, muchas gracias, Luis

Jorge L. Dunkelman 09 febrero, 2011 20:02  

Luis,

fijate en las indicaciones en el enlace Ayuda.

Anónimo,  07 septiembre, 2011 04:29  

he creado una tabla similar pero con nombres quizas más complejos (alfanuméricos) y algunos de ellos - la mayoría- me los muestra en el menú desplegable pero no me sale el siguiente menú desplegable.

que puedo hacer? esos mismos les pongo un nombre sencillo como - casa- y me va bien.

gracias

Jorge L. Dunkelman 10 septiembre, 2011 18:46  

Los nombred deben seguir ciertas reglas.Por ejemplo, no pueden coincidir con direcciones de celdas (por ejemplo, A1) o empezar con un número.

matrio 19 diciembre, 2011 15:44  

Mi pregunta es como conseguir que la rueda del ratón funcione a la hora de seleccionar un valor dentro de la lista desplegable. O incluso q tecleando la primera letra saltara al valor que empiece por la misma.

Esto sería bastante útil para listas largas. Gracias.

Jorge L. Dunkelman 19 diciembre, 2011 19:59  

Fijate en esta nota. En cuanto a la rueda del ratón, no conozco ninguna técnica para hacerlo.

damaso 06 febrero, 2012 02:20  

Jorge, dejame felicitarte por tu blog, eres un buen sensei,pero tengo un problema al hacer las listas desplegables dependientes. Estoy haciendo uno para 3 listas desplegables dependientes, el caso es que no me funciona la ultima lista como debe ser. La primera se trata de departamentos, la segunda de provincias y la ultima de distritos;creo que el error esta en que algunos distritos tienen el mismo nombre de las provincias, por lo tanto para estos casos cuando selecciono una provincia con el mismo nombre del distrito y trato de seleccionar el distrito me sale la lista que le correponde a las provincias. ¿Como puedo hacer para que no suceda esto y solo aparezca la celda que se debe. Saludos ;)

Jorge L. Dunkelman 06 febrero, 2012 06:58  

Damaso,
efectivamente, los nombres tienen que ser únicos. Lo que podrías hacer es agregar un caracter, por ejemplo "_" al final del nombre y usar & o CONCATENAR para componer el nombre.

pedro linares 12 marzo, 2012 13:59  

Hola Jorge,

Ante todo, gracias por tu excelente aportación.
Tengo varias consultas:
- ¿Como puedo definir que me aparezca una opción por defecto en mis desplegables? Algo como "Selecciona una opción"
- Ahora mismo tengo 3 niveles enlazados de selección. Quiero saber si puedo efectuar cálculos según las selecciones que se hagan. Por ejemplo:

Un usuario escoge inicialmente la opción 1 a la que le asignamos el valor 2. Después:
X opción 1.1: +1
Y opción 1.2: -1
Y opción 2.1: +2
X opción 2.2: -2

Si escogen lo marcado con X, total= 1
Si escogen lo marcado con Y, total= 3

No se si me he explicado. Resumiendo, es que se puedan hacer cálculos según las opciones seleccionadas.

Saludos!

Jorge L. Dunkelman 13 marzo, 2012 19:34  

Hola

1 - poniendo como primer valor de lista el texto. Claro, que si el usuario lo elige eso es lo que aparecerá en la celda. Otra solución es más adecuada es usar la propiedad "Mensaje de entrada" de la validación de datos. De esta manera, cuando el usuario selecciona la celda aparece el mensaje "Selecciona un opción".

2 - Los cálculos se efectuan en las celdas con fórmulas, no con validación de datos.

pedro linares 14 marzo, 2012 10:27  

Hola de nuevo,

- He probado a activar el mensaje de entrada, pero cuando paso por encima no me activa el mensaje.

- Cálculos: lo pruebo a ver si me sale.

muchas gracias!

Jorge L. Dunkelman 14 marzo, 2012 13:53  

Ek mensaje aparece cuando se selecciona la celda.

Anónimo,  26 junio, 2012 06:42  

Estimado Profesor

El problema que tengo, es que hay provincias que tienen el mismo nombre que el departamento, entonces cuando escojo una provincia en vez de salir los distritos que pertenecen a esa provincia, vuelven a salir las provincias que pertenecen al departamento.

Agradezco de antemano tu respuesta.
Mil Gracias

Daniel Gianella

Jorge L. Dunkelman 29 junio, 2012 18:17  

Lo más fácil sería agregar un sufijo al departamento para distinguirlo, por ejemplo XXX_dep, o cambiar la denominación a Departamento de XXXX, para lograr nombres únicos.

Fede Romano,  11 julio, 2012 20:54  

Muchas gracias por todas las ayudas que se ven en el blog.
Ahora, a mi me surge la duda de como hacer esto mismo pero con un lista activeX.
Pasa que tengo todo un formulario hecho con ACTIVE X y me gustaría seguir de la misma manera.

En mi caso, el formulario esta en la hoja "Carga Datos". Y las opciones de desplegables las tengo en la hoja "Opciones Desplegables".
Las opciones de 1er nivel estan en las celdas C1:C5, mientras que las que corresponden al segundo nivel estan en las celdas E1:E6

Se puede hacer algo para esto???

muchas gracias

Jorge L. Dunkelman 12 julio, 2012 11:44  

Tendrías que programar un evento que determine el rango de datos (RowSource). Es menos complicado de lo que suena pero requiere que tengas conocimientos de Vba. Tal vez publique una nota sobre el tema.

Anónimo,  14 julio, 2012 00:45  

Querido Jorge

Tengo un inconveniente. Poseo dos columnas, una de gerencia y la otra del área; ya realicé la lista dependiente, pero en la columna de la gerencia me salen los nombres con "_" Ej: Gerencia_corporativa_ambiental quisiera que saliera el nombre sin este inconveniente. Muchas gracias por su colaboración.

Jorge L. Dunkelman 14 julio, 2012 11:07  

En la nota muestro como usar SUSTITUIR para evitar eso. Los nombres (o rangos nominados) en Excel no pueden tener espacios entre las palabras.

Gustav Ma 15 julio, 2012 06:21  

Maestro, excelente blog. Como seria la implementación de este mismo caso pero en un formulario, donde las listas se obtengan a traves de combobox.
Lo agradecería mucho

Jorge L. Dunkelman 15 julio, 2012 18:50  

Estoy preparando una nota sobre el tema. Espero publicarla en breve.

Javier D 14 agosto, 2012 18:59  

Te felicito por tu trabajo, quisiera pedirte ayuda ya que la siguiente macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A5"), Target).Address = Range("A5").Address Then
Range("B5").ClearContents
End If
End Sub

Permite borrar solo en el caso que la celda A5 cambie de valor, pero que sucede si deseo que me borre el contenido según la celda de la columna A que yo escoja dentro de un rango ejemplo A5 y A10.

Por si no me diera a entender bien dentro del rango de celdas entre A5 y A10 al producir un cambio en la celda A6 me limpie la celda B6 o si el cambio se realice en la celda A9 limpie la celda B9.

De antemano Gracias

Jorge L. Dunkelman 19 agosto, 2012 16:18  

Espero haber entendido; sería

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A5:A10"), Target).Address = Range("A5:A10").Address Then
Target.Offset(0,1).ClearContents
End If
End Sub

Saúl,  05 octubre, 2012 21:57  

Hola Jorge,

gracias por el blog, es espectacular :)

una duda, trabajo con una lista múltiple, pero d manera diferente:


Clase de papel: Estucado / Cartulina / Adhesivo

Color: blanco (para todos) / fluorescente (solo para cartulina y adhesivo) / marfil (solo para estucado y cartulina)

Formato: 65x90 (para todos) / A3 (para adhesivo) / A4 (para estuucado y cartulina)

como puedo hacer que 2 o más variables dependan de un valor inicial, que sería en este caso la clase de papel?



Anónimo,  06 octubre, 2012 10:12  

simplemente, enhorabuena por el blog

Anónimo,  06 octubre, 2012 11:17  

hola,

una duda: como hacer que respecto a una misma entrada "pais" pueda obtener diferentes listas dependientes, por ejemplo "ciudades", "rios", "playas"

gracias de antemano!!!

Jorge L. Dunkelman 06 octubre, 2012 15:37  

Es lo que está explicado en la nota. ¿O no entendí la consulta?

Jorge L. Dunkelman 06 octubre, 2012 15:41  

Saúl,
es lo que muestro en la nota.

Anónimo,  07 octubre, 2012 09:53  

gracias Jorge,

pero según la nota, las listas son dependientes segun el esquema:
ciudades < paises < continentes

y lo que yo pretendo es

ciudades < paises
rios < paises
playas < paises

es decir, tener varias salidas dependientes de una misma entrada.

no sé si me explico...

gracias

Saúl

Anónimo,  07 octubre, 2012 12:01  

Hola,

en tu explicación, la 3ª elección viene determinada por la 2ª, es decir: la ciudad pertenece al país. lo que yo quiero es que la 2ª, 3ª y sucesivas, estén solo en función de la 1ª... no veo la forma...

gracias

Saúl

Jorge L. Dunkelman 09 octubre, 2012 13:40  

Tendrías que agregar una lista más con los valores "ciudades,rios, playas" y crear listas combinadas de pais/ciudades, pais/rios, pais/playas.
La técnica es similar a lo mostrado en la nota, pero una pexplicación detallada excede los marcos de un comentario.

Anónimo,  10 noviembre, 2012 19:54  

si quiero eliminar los espacios de la lista desde la primera lista, es decir donde no se ha utilizado la funcion indirecto, como hacerlo?

Jorge L. Dunkelman 12 noviembre, 2012 07:06  

Supongo que te refieres a las listas de las ciudades. Como cada lista tiene un número distinto de filas podemos usar el siguiente truco:
1 - Seleccionamos alguna celda de alguna de las listas;
2 - Apretamos Ctrl+* para seleccionar todo el área que ocupan las listas;
3 - Apretamos F5--Especial--Constantes y Aceptar. Al hacer esto Excel selecciona todas las celdas del área que no están vacías.
4 - Creamos los nombres tal como está explicado en la nota.

Anónimo,  13 noviembre, 2012 23:32  

Que pasa si uno de mis datos es numerico, no me deja crear nombres numericos...
Mis Continentes = Materiales
Mis Paises = Calibres(numeros) para esos materiales
Mis Ciudades = Productos viables para esos calibres.

Me puedes ayudar?

Jorge L. Dunkelman 16 noviembre, 2012 19:08  

El nombre definido se refiere a un rango sin tomar en cuenta si éste contiene valores numéricos, texto o ambos. Así que se trata de algún otro problema.

Yang Min Jin Li 24 noviembre, 2012 19:29  

Muy buen blog! pero me ha surgido algo mas complicado...

Como puedo hacer que la lista que escoja dependa de dos celdas?

Un ejemplo:

tengo dos ciudades con el MISMO nombre, pero quiero asignarle una lista a cada país, por lo que debería de depender de las dos celdas, pero al buscar el nombre en la celda no me sirve con que dependa de una sola lista.

Por ejemplo: (ficticio, por supuesto!)
Países; Provincias; ciudades
España; Comunidad Valenciana; Valencia, Castellón
Francia; abc, bcd, cde
Italia; Comunidad Valenciana; Nápoles, Roma

Quiero decir, como puedo hacer que el valor de PROVINCIA dependa igualmente del pais, sin tener que asignarles nombres diferentes?

Jorge L. Dunkelman 26 noviembre, 2012 17:56  

No me queda claro. El orden jerárquico es País-Provincia-Ciudad. Así que la provincia depende del país elegido; como la ciudad depende de la provincia (y ésta del país), no tendría que haber ningún problema.

Anónimo,  14 febrero, 2013 16:31  

Buenas y gracias ante todo.

Mirando este código:
Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("A5"), Target).Address = Range("A5").Address Then
Range("B5").ClearContents
End If
End Sub

Y si lo que quiero no es borrar el contenido de la celda B5 si no que en dicha celda aparezca el primer valor de la lista que le ha sido asignada?
Gracias

Jorge L. Dunkelman 14 febrero, 2013 17:23  

Tendrías que reemplazar la sentencia

Range("B5").ClearContents

por

Range("B5")="referencia al primer valor de la lista"

donde la referencia puede ser la dirección de una celda (por ejemplo $A$1), el primer elemento de un rango definido con un nombre; por ejemplo, si definimos el nombre "meses" que se refiere a un rango que contiene los meses del año, para que aparezca "enero" pondríamos

Range("B5")=Range("meses")(1)

Anónimo,  14 febrero, 2013 17:36  

Jorge,
es algo similar a lo que busco, pero la lista en la que quiero aparezca el primer valor es dependiente, es decir, en B5 se crea una lista en función del valor de A5, luego lo que busco es que al desplegar A5, en B5 aparezca el primer valor de la lista asignada. En el ejemplo de los paises, si selecciono el continente Europa, quiero que por defecto en la lista de países me aparezca España para que el usuario solo tenga que modificarlo en caso de que no sea España el país deseado. De la otra forma siempre ha de modificarlo.

Gracias de nuevo

Jorge L. Dunkelman 15 febrero, 2013 12:00  

Estaré publicando una nota mostrando el código necesario.

Anónimo,  09 marzo, 2013 23:28  

hola, buenas tardes, tengo un problema con las listas desplegables en excel,trabajo en una escuela y tengo que entregar este tipo de listas con calificaciones de grupos, pero al poner la calificación de un alumno del grupo "A" se refleja también en la de un alumno del grupo "B" por citar un ejemplo y si corrijo el de "B" se modifica también el de "A", que puedo hacer para que no se afecten los resultados de las otras listas. todas las listas están en el mismo libro, yo le voy dando click a una celda y se van cambiando las listas, "A" "B" "C", etc. saludos y gracias de antemano.

Jorge L. Dunkelman 10 marzo, 2013 07:21  

Bien, muy difícil de ver cuál pueda ser el problema a partir de la descripción que das. Te sugiero que me mandes el archivo para que pueda hacerme una idea más cabal del problema (fijate en el botón Ayuda en la parte superior del blog).

Anónimo,  18 marzo, 2013 00:42  

Hola, tengo un bloqueo mental y me gustaría un poco de ayuda, las listas despegables dependientes, las puedo hacer si todos los datos son diferentes, pero mi problema esta en lo siguiente:
tengo 3 carreras, topografía, diseño, edificaciones; cada carrera tiene de 4 a 6 semestres (ejemplo I, II, III) y cada semestre diferentes cursos, lo que quiero hacer y no me sale es: carrera------semestres-------curso, osea lista desplegable para carrera, al escoger carrera que solo me aparezcan los semestres que tiene, y al escoger semestre, que solo me aparezcan los cursos de ese semestre para esa carrera en particular y poder escoger el curso, además me gustaría que en semestre siempre mantenga el formato I, II, III
gracias de antemano sea cual sea la respuesta

Jorge L. Dunkelman 18 marzo, 2013 07:15  

La respuesta es usar las técnicas que muestro en esta nota. Una explicación detallada de tu consulta excede el marco de un comentario. Pero en términos generales, un rango que contenga las carreras, rangos que contienen los semestres dependiendo de la carrera (por ejemplo si se elige "topografía", el nombre del rango invocado seria "topogafia_semestres") y otros rangos conteniendo los cursos que dependan de la elección anterior.
Tal vez pulique una nota mostrando la técnica.

Anónimo,  30 abril, 2013 01:42  

Hola jorge. Mil gracias por tu NOTA. La aplique y me funcionó perfecto!!!

Me encanta trabajar en excel y estas ayudas son super útiles.

Seguiré con mi trabajo y cuando tenga una duda no dudare recurrir nuevamente a ti!

Ernesto Agudelo,  09 mayo, 2013 23:54  

Buenas tardes Jorge, mi consulta es la siguiente, como hago para que cuando cambie un dato de la lista independiente lo de la lista dependiente se me borre y no me quede los datos que tenían, ya que cuando borro lo de la lista independiente el dato de la lista dependiente se queda y no desaparece

Jorge Dunkelman 10 mayo, 2013 18:37  

Fijate en la técnica que muestro en esta nota.

Jorge Dunkelman 30 mayo, 2013 08:32  

La respuetsa aparece en uno de los comentrarios de esta nota, pero te sugiero que veas esta nota, donde explico la técnica en forma más detallada.
También te sugiero leer esta nota donde muestro una implementación completa.
También podés adquirir mi guía completa sobre listas desplegables.

Moises per 24 enero, 2014 02:04  

Hola de nuevo, gracias por tus aportes. He mirado como me dijistes todo lo relacionado con listas desplegables dependientes pero no consigo solucionar mi problema. Yo estoy generando una factura pero que a cada fila donde se ponen los productos utilizados hay una lista dependiente, por ahora lo quiero hacer con 40 listas dependientes que cada una primero pregunta por una categoria y luego segun la categoria en el segun desplegable salen los productos de esa categoria. Y entonces sale el precio de ese producto. El problema es que necesito que todo sea dinamico y que la entrada de datos sea lo mas simple posible pues es para usuarios muy principiantes. Hasta ahora he probado todos tus sistemas de listas desplegables pero cuando he de usar indirecto con algo que es dinamico me da problema porque no evalua rangos dinamicos. Entonces pensé voy a hacerlo con tablas pero la snormales no me solucion el problema porque deberia de partirlo todo en varias tablas perdiendo la dependencia de las listas y lo he intentado con una tabla dinamica pero entonces indirecto se vuelve a quejar. Supongo que no me he acabado de explicar bien pero si ves por donde voy y una posible solucion estaria tremendamente agradecido.

Jorge Dunkelman 24 enero, 2014 11:13  

Bien, espero haber entendido. En lo que hace a los rangos dinámicos con la función INDIRECTO, en esta misma nota, al final, explico la técnica para superar el problema. También las tablas (listas en Excel 2003) pueden usarse en listas desplegables dependientes múltiples como muestro en esta nota.
Si todo esto no te ayuda, ponete en contacto conmigo por mail privado (ver "Ayuda" en la parte superior del blog).

Anónimo,  03 febrero, 2014 22:24  

Buenas tardes, Tengo la siguiente inquietud. Tengo un atabla compuesta pro 3 columnas: A: Color de pigmento, B: Proveedor y C Código del Producto. En una hoja de control de producción he colocado listas desplegables para las columnas A y B que son independientes entre si, ya que todos los proveedores fabrican todos los colores, pero deseo que en base a los valores de éstas, me aparezca en otra celda, como lista desplegable, las opciones de códigos de color correspondientes a ese proveedor y en ese color. Que función o funciones debo utilizar?

Jorge Dunkelman 04 febrero, 2014 07:22  

Siguiendo con la técnica que muestro en esta nota, tendrías que crear rangos con valores para cada combinación (A&B) y crear los nombres que se refieran a estos rangos. En una celda tendrías que concatenar los valores seleccionados en A y B y usar este valor con INDIRECTO para crear la lista, tal como se muestra en la nota.

Anne 03 marzo, 2014 20:41  

Buen dia, tengo la siguiente cuestion, cuento com un tabla compuesta por 4 columnas: Centro de costo, descripcion de centro de costo, Cuenta contable, y descripcion de cuenta contable. ¿como podria validar la cuarta columna?

Jorge Dunkelman 04 marzo, 2014 07:22  

Anne, ¿qué quieres decir con validar la cuarta columna? ¿Verificar que la descripción de la cuenta sea la correcta?

Anne 04 marzo, 2014 16:08  

Tengo 4 columnas(listas desplegables): Centro de costo, descripción de centro de costo, Cuenta contable, y descripción de cuenta contable. Cada una dependiente de la anterior respectivamente, me falta validar la lista desplegable "descripción de cuenta contable" que es dependiente de "Cuenta contable". Mas sin embargo las descripciones de dicha lista desplegable llegan a repetirse en varias cuentas contables.

Jorge Dunkelman 04 marzo, 2014 20:09  

Anne, la descripción de la cuenta es única para cada cuenta por lo que no necesitas validación de datos sino poner la descripción con alguna función de búsquedda como BUSCARV o INDICE.
Si no queda claro te sugiero que sigamos con la consulta por mail privado (fijate en el enlace Ayuda, en la parte superior de la plantilla).

Anónimo,  26 agosto, 2014 19:32  

Hola nuevamente, accedí al post y realicé los pasos mencionados sin problema, sin embargo, me sale el error : "El origen actualmente evalúa un error ¿desea continuar?" al señalarse que sí, no pasa nada, no me genera los datos en el segundo nivel. qué debo incluir en las opciones de excel o qué estoy omitiendo?. la primera lista queda ok (continentes), pero la segunda (países) no funciona.
Gracias!

Anónimo,  26 agosto, 2014 20:12  

Gracias, ya encontré mi error. Las ciudades tenían tíldes, al quitarlas me genera la lista, gracias!

Leba Leba 08 septiembre, 2014 19:58  

buenos días

tengo un rango amplio para aplicar la lista despegable y deseo que al cambiar el valor de una celta la otra quede en blanco, trate con la macro :

Private Sub Worksheet_Change(ByVal Target As Range)
If Union(Range("C7:C13"), Target).Address = Range("C7:C13").Address Then
Range("D7:D13").ClearContents
End If
End Sub

pero me modifica toda la D no se cual sea mi error si se modifica c7 solo afecte d7 si se modifica c8 solo d8, etc...

Podria apoyarme por favor

Jorge Dunkelman 09 septiembre, 2014 07:15  

Al poner Range("D7:D13").ClearContents estás borrando el contenido de todas las celdas en el rango. En su lugar tenés que usar

Target.Offset(0,1).ClearContents

Anónimo,  03 octubre, 2014 17:12  

Buenos dias señor Jorge

Tengo la siguiente dificultad, tengo una lista desplegable con 10 productos para escoger un producto, pero y si quiero escoger varios productos de la misma lista ¿como lo puedo hacer?

Jorge Dunkelman 03 octubre, 2014 19:08  

Para seleccionar varios productos al mismo tiempo tendrías que usar el control ListBox (Cuadro de lista) de la colección ActiveX que permite hacer selecciones múltiples.
Pero, ¿qué pasa luego con los productos seleccionados? ¿Aparecen como cadena de texto en una celda, se insertan cada uno en una celda distinta?

Nora Herrera 04 octubre, 2014 20:37  

Buen Día Jorge, que pasa si en una lista desplegable tengo muchos concepto y es muy tardado llegar al que necesito... como puedo con solo teclear la primer letra, que me lleve a ese rango para escoger lo que necesito y no irme buscando...

Mil Gracias por tu atención...

Anónimo,  08 noviembre, 2014 13:16  

Buenos días Jorge,

Para empezar quería felicitarte por el blog. Esta misma entrada lleva 4 años posteada y tú sigues respondiendo, increíble.

Mi pregunta es la siguiente: ¿Es posible hacer que un nombre sea dependiente del contenido de una celda?
Me explico con un ejemplo: en la entrada se define el nombre "América_del_Norte", a partir del cual luego se obtendrán los distintos países. Sin embargo, si alguien modificase esa celda por "América_Norte" el programa no funcionaría hasta que se actualizase el nombre correspondiente. ¿Hay alguna forma de realizar ese proceso automáticamente?

Un saludo y muchas gracias,

Fernando

Jorge Dunkelman 09 noviembre, 2014 15:39  

Fernando, no me queda claro. ¿La idea es que si el usuario introduce "America_Norte" el valor se cambie automáticamente a "America_del_Norte"?

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP