Hojas de cálculo en Excel - página principal

Validación de listas dependientes

Bajo el nombre de validación de listas dependientes, os explicaré como podemos validar datos de forma automática en una lista desplegable, que a su vez requiera de la interacción con otra lista desplegable. Supongamos que tenemos dos listas de datos, una lista corresponderá a los datos padre y la otra a los datos hijos. Dependiendo de los datos padre que seleccionemos en el desplegable, nos aparecerán unos u otros datos hijos de forma automática.

Como esto parece algo farragoso, será mejor explicarlo con un sencillo ejemplo. Supongamos que tenemos varias marcas de automóviles, y en cada una de ellas, tenemos un grupo de modelos de cada marca. Algo así como esto:


Lo que pretendemos hacer a partir de esos datos, es montar dos listas desplegables, una con las marcas de automóviles, y otra con los modelos de cada una de las marcas, de tal forma que si elegimos una marca en concreto, en la otra lista solo nos aparezcan los modelos de esa marca.

Lo primero que haremos será crear la lista de validación de las marcas de automóviles, para que nadie pueda introducir marcas que no tengamos en nuestro listado (recordemos que estamos validando datos). Para ello, nos situaremos por ejemplo en la celda E12, y lo que haremos será definir la lista desde el menú Datos, eligiendo a continuación Validación…, y en la pestaña Configuración, que es la que nos saldrá por defecto en primer plano, elegiremos en Permitir la opción Lista, y en Origen seleccionaremos el rango comprendido entre B3 y D3 que corresponde a los nombres de las marcas que tenemos, tal y como se puede observar en la imagen anterior. Si queréis una ayuda gráfica, lo que tenéis que poner es esto que se ve en la siguiente imagen:


Es decir, habremos creado este desplegable en E12:


Una vez hecho esto, ahora solo nos quedará crear el desplegable correspondiente a los modelos de cada marca. Para ello, crearemos unos nombres, para cada grupo de modelos de cada marca. Esto es imprescindible, porque usaremos posteriormente la función indirecto (luego explicaré para que nos va a servir). Para definir cada rango de datos con un nombre, la forma más sencilla es hacer lo siguiente:

Seleccionaremos cada grupo homogéneo de modelos, partiendo por ejemplo con los modelos de coche de Ferrari, y seleccionando desde la celda B4 hasta la B6. Una vez tengamos seleccionados los modelos (ver la imagen siguiente), tendremos que ponerles un nombre. Necesariamente ese nombre debe coincidir con el que hemos utilizado en el encabezado de cada grupo, es decir, en este caso debe llamarse Ferrari, …no puede llamarse Pepito, ni Audi. Para ponerle un nombre a ese rango, tan solo deberemos escribirlo en la zona enmarcada en rojo, y pulsar intro (ver la siguiente imagen):


Haremos lo mismo con el rango que va desde C4 a C6, y le pondremos como nombre la marca correspondiente, es decir, Lamborghini. Finalmente haremos lo mismo con el rango de datos comprendido entre D4 y D7, al que le pondremos como nombre Porsche.

Una vez hayamos definido todos los nombres, podremos comprobarlos. Para ello nos situaremos en cualquier celda, por ejemplo en E6, y desde el menú Insertar, seleccionaremos Nombre, y a continuación Definir…. Si lo hemos hecho todo correctamente, veremos algo como esto:


Ahora ya tenemos todo lo necesario para crear la lista dependiente correspondiente a los modelos de cada marca. Para ello nos situaremos en la celda E14, y desde el menú Datos, elegiremos a continuación Validación…, y en la pestaña Configuración, que es la que nos saldrá por defecto en primer plano, elegiremos en Permitir la opción Lista, y en Origen escribiremos =INDIRECTO(E12), tal y como muestra la siguiente imagen:


La función indirecto, al utilizarla como origen de datos referenciada a la celda E12 que es donde tenemos las marcas de coches, lo que va a hacer es mostrarnos solo las correspondientes a la marca seleccionada (recordemos que en E12 tenemos el primer desplegable donde seleccionaremos una marca en concreto). Es decir, =INDIRECTO(E12) lo que va a hacer es buscar si existe un nombre (recordemos que precisamente hemos definido para esto los nombres anteriores) Ferrari, Lamborghini, o Porsche, y nos va a mostrar los datos correspondientes a ese nombre, que en este caso coinciden y además de forma necesaria, con cada marca en cuestión, tal y como comentábamos anteriormente.

Si lo hemos hecho todo correctamente, cada vez que seleccionemos una marca, el segundo desplegable, es decir, la lista dependiente (pues la segunda, la de los modelos de coche, depende de la primera, es decir, de las marcas de automóviles), nos mostrará los modelos específicos de la marca seleccionada. Algo como lo que muestra la siguiente imagen:


Finalmente, para darle un toque de elegancia a nuestros desplegables, haremos que cada vez que se cambie la marca de coches, se elimine el modelo de vehículo que habíamos seleccionado anteriormente, si es que hemos estado jugando con los deplegables. Para ello tan solo tendremos que copiar y pegar este código pero no en un módulo, sino en la Hoja1, pues es en ella donde tenemos los desplegables:


Private Sub Worksheet_Change(ByVal Target As Range)
'Si ha errores, que continúe
On Error Resume Next
'Si cambiamos el dato de la celda E12,
'limpiamos el segundo desplegable

If Target.Cells = Range("E12") Then
'eliminamos el contenido de E14
Range("E14").ClearContents
End If
End Sub

Desde aquí podéis descargar el fichero de excel, con el ejemplo que os presento en este artículo, para que podáis ver en funcionamiento como se crean listas de validación dependientes.



46 comentarios:

Anónimo dijo...

Hola, he tomado tu ejemplo y me ha servido mucho. Encuentro muy interesante el final (borrar los datos que no corresponden)y quisiera aplicarlo para un ejemplo donde la validación no se aplica para un solo dato sino para la alimentación de una base de datos (hacia abajo en dos columnas) así:
Marca: ferrari / Modelo: se llena con el desplegable
Marca: porsche / Modelo: se llena con el desplegable

etc...

mi correo es dgomez_78@hotmail.com

esteban dijo...

hola como esta maestro sus codigo y ejemplos han hecho y que hoy sepa hacer buen codigo vba,que siera si puedes ayudarme con una duda como saber el nombre usuario de el perfil que esta usando el fichero (usuario de windows)

Ministro dijo...

Muy interesante este ejemplo.
Es de mucha utilidad.
Gracias
José Luis

Javier Marco dijo...

Muchas gracias por tu comentario, Ministro.

Hola Germán. Prueba esto:

Sub ejemplo()
usuario = MsgBox(Application.UserName)
End Sub


Salu2 a to2

Anónimo dijo...

hola, como se haria esto mismo pero permitiendo que solo se seleecione una vez el modelo de la marca en un rango determinado?

gracias.

Anónimo dijo...

Estimado, muy buena la ayuda, pero, no queda claro como configurar para que funcione, la limpieza de los datos cuando cambio la selección de la marca de auto. Sugiero que puedas ayudarnos a detallar paso a paso como hacerlo praa tener completa la ayuda.

Muchas gracias.

Javier Marco dijo...

Pues simplemente debes copiar y pegar el último código que aparece en el artículo, pero no en un módulo, sino en la Hoja1, evidentemente, desde VBA.

Saludos.

Anónimo dijo...

Hola, genial el blog y este post en concreto muy útil. Lo único que le falta para ser perfecto es cómo hacerlo cuando tienes los datos en una tabla con mas datos, y no ya divididos por marcas.

Unknown dijo...

Hola, muy bueno tu blog, me ha servido mucho los ejemplos y explicaciones que das. Muy interesante la validación de listas dependientes y quisiera aplicarlo a un formulario que estoy desarrollando. En el formulario tengo dos combobox, en uno selecciono paises y en el otro ciudades, quisiera que cuando seleccione un determinado país en el otro combobox solamente se muestren las ciudades de ese país y no todas las ciudades que tengo en el listado de excel. ¿Como puedo lograr esto?. Aplique tu ejemplo y lo grabe en una macro, despues desde el formulario hice el llamado a la macro y no me funciono. Me puedes ayudar. Muchas gracias.

Javier Marco dijo...

En el caso de tratarse de un combobox, la cosa varía un poco.

Aquí te dejo un ejemplo que he hecho. Espero que te sea útil, ...ahí va el enlace (el código está comentado):

http://www.megaupload.com/es/?d=ONLO0YYW

Un saludo.

Unknown dijo...

Muchas gracias Javier, me fue muy util el ejemplo que dejaste.
Te seguire molestando si me surgen más dudas, jajaja. Nuevamente muchas gracias.

Javier Marco dijo...

Me alegro que te haya servido.

Ten en cuenta que solo contesto aquellas preguntas a las que sé darles respuesta, es obvio ;-), aquellas que no requieren un trabajo excesivo de desarrollo, y sobretodo aquellas preguntas que pueden ser útiles al resto de lectores del blog.

Gracias por tu comentario.

Anónimo dijo...

Hay un pequeño problema.
Una vez que eliges una marca, ya no puedes elegir otra.
¿Existe alguna forma de "resetear" y volver a empezar de nuevo?

Javier Marco dijo...

Tu versión de excel debe tener algún problema, porque puedes elegir tantas veces como desees una marca diferente, sin necesidad de tener que seleccionar el modelo de la primera marca seleccionada. Es decir, si eliges "Porsche", y te arrepientes, y decides cambiarlo, y elegir "Ferrari", puedes hacerlo perfectamente. Me resulta extraña tu pregunta, porque es un desplegable cuyo comportamiento es idéntico a cualquier otro.

PD: Yo uso Excel 2002 (comunmente llamada Excel XP), y sin problema.

Anónimo dijo...

¡Yo soy el problema de mi versión de Excel!
He descubierto que ni el espacio, ni el símbolo / pueden formar parte del nombre del rango.
He puesto nombres normales, separados por _ y todo funciona perfectamente.
Gracias.

Javier Marco dijo...

Me alegro que finalmente hayas podido solucionarlo. Existen una serie de caracteres raros que a windows no le gustan, no solo para temas como el que planteas, sino incluso para el nombre de los propios archivos.

Saludos.

Anónimo dijo...

Muy bueno el ejemplo y la explicación, solo tengo un problema, en la selección de las lista selecciono celdas en blanco para porteriores incorporaciones de elementos, esas celdas vaicas salen en la lista selección, ¿hay algún modo de eliminar o que no salga esas líneas en blanco de las celdas vacias?
Gracias anticipadas.

Javier Marco dijo...

No deberías dejar esas filas en blanco, para ñadir en un futuro, nuevos items. Podrías crearte un formulario, para ir insertando líneas a esa lista de validación.

Anónimo dijo...

Excelente, quisiera saber si puedo, en el cuadro de validacion de datos, casilla origen, referenciar otros books y otras hojas. Mi correo sitial@gmail.com

Anónimo dijo...

HOla, como lo hago si lo que necesito referenciar esta en otra hoja?............por que lo que necesito son maquinas y sus equipos, pero el problema es que las maquinas son las hojas (sheet1, sheet2, sheet3) y sus equipos estan en un listado en las respectivas hojas....como lo hago?........por que no se me esta permitido cambiar nada del formato de la hoja de calculo.

chao gracias

Anónimo dijo...

muy buenos y claros tus ejemplos...y quiero saber si me puedes ayudar en algo, lo que pasa es que quiero que con un combobox se seleccione una hoja y con otro combobox una celda de esa hoja, pero cuando la eliga quiero que vaya exactamente a esa hoja y celda(Con UserForm). Tengo listo los combobox, con tus ejemplos me quedo clarito, me falta que vaya a la hoja y celda elegida.
Gracias

Unknown dijo...

Excelente tu bloj JLD

Felicitaciones.

Unknown dijo...

buenas tardes, tengo una base de datos muy grande y tuve que repartirla en dos hojas, ahora tengo que asignar una columna completa (es decir de las dos hojas) pero cuando lo asigno para crar la lista, no me aparce mas que puntos suspensivos en el administrador de archivos. Si es posible asignar rangos de dos hojas diferentes? o que debo hacer, Gracias

Anónimo dijo...

Enhorabuena, tu blog es fantástico y muy útil, como este post en concreto. La duda que tengo es exáctamente la misma que el primer comentario, que la macro no haga referencia a una sola celda, sino para rellenar 2 columnas hacia abajo.

¿Alguna sugerencia?

Muchas gracias!

machodiego@gmail.com

Nico Reyes dijo...

espectacular, hace mucho venia tratando de hacer esto, me fue muy util. Muchas gracias!!

Unknown dijo...

Estimado amigazo, muchas gracias ha sido de ayuda, pero tengo un problema al aplicar el INDIRECTO, lo hice todo tal cual y no me resulta, me dice que tiene un error sin especificar cual.cree los nombres y los verifique y todo bien, luego valido con indirecto tal cual lo muestras en el ejemplo y al elegir un nombre en la primera lista despegable,en la segunda me aparece el mismo nombre sin desplegar nada, trato de modificar nombres, todos escritos igual, y nada...a lo mas he logrado que no me aparezca nada en vez del mismo nombre de la primera lista.

FJPPitta dijo...

Que tal, todo funciono de maravilla, muy clara tu guia. Hay forma de poner el contenido de las listas en una hoja aparte?. Tengo la hoja principal con mis 4 listas pero estoy haciendo otra hoja con la informacion de 2 listas y quisiera reutilizarla.

Javier Marco dijo...

Mírate esto: Validación con datos en otra hoja.

Anónimo dijo...

Muy buena la info, me fue de utilidad

Anónimo dijo...

Hola, me gustaria saber si me puedes ayudar con el siguiente problema:
Necesito que se despliegue una lista con información que se encuentra en otra hoja. Pero la lista que se encuentra en la otra hoja puede cambiar (No puedo definir el rango con una etiqueta, pues el rango puede cambiar de acuerdo a nueva información que vaya incorporando).

Espero puedas ayudarme

Anónimo dijo...

Hola.

Sigo los pasos que indicas "al pié de la letra" y cuando valido la casilla E12, me sale el siguiente mensaje de error: "El origen actualmente evalúa un error ¿Desea continuar? SI - NO"

¿A que puede ser debido?

Muchas gracias.

Javier Marco dijo...

¿Pero eso te ocurre con el ejemplo que colgué, o con otro que has hecho tú partiendo de cero?. El ejemplo que colgué funciona correctamente.

Anónimo dijo...

Hola Javier Marco y gracias por contestar.

Como te he dicho en mi primer mensaje de consulta, he seguido tu ejemplo al "pié de la letra" y me sale el error. Pero aún así, he realizado otros ejemplos que he encontrado en internet, y también me pasa.

La única solución para que no me salga el error es, entrecomillar el E12, es decir, =INDIRECTO("E12"), pero claro, ya la fórmula que indicas para que se abra la ventana evidentemente, no funciona.

Yo me pregunto: ¿Abrá que preparar la hoja de excel con algún tipo de configuración antes de poner en práctica el ejercicio?

P.D.: Lo he probado en Office 2.003 y 2.007 y en los dos me ocurre.

Saludos.

Anónimo dijo...

Hola Javier Marco.

Ya he conseguido poner a prueba la fórmula del INDIRECTO. ¿Sabes cual era el problema? Pues que la palabra que tenía que coger como "base de dato", estaba separa y por ello no dejaba aplicar la fórmula.

Un saludo.

Nacho Durán dijo...

Hola maestro!

Tengo un problemilla a ver si puedes solucionarmelo. Tengo unas listas dependientes, tres para ser exactos, que me funcionan perfectamente siempre que lo haga en la misma hoja, pero lo necesito en otra hoja, osea, separar los datos (que son muchisimos) en una hoja y las listas dependientes en otra. He visto tu ejemplo de lista tomando datos de otra hoja, pero no listas dependientes. ¿como puedo hacerlo?

Unknown dijo...

Quisiera saber como le hago para definir nombres de listas con espacios en blanco y poder llamarlas luego para usar la funcion de indirecto.

Gracias!!!

Txabi dijo...

Muy buenas Javier!!, por depronto muchisimas gracias por tus sabios consejos, los cuales me ayudan basatante a la hora de ponerme a crear un excel para hacer una serie calculos de energias.
A llegado un punto en el que me he atascado, y seguramente sea una tonteria lo que necesito sacar, te esplico:
Tengo dos listas dependientes ya creadas en la "hoja2", basandome en tus ejemplos, las cuales tiran de una tabla creada en la "hoja1".
Lo que me gustaria, es que despues de filtrar a traves de las listas dependientes en "hoja2", me diese los valores correspondientes a esa filtracion que saldrian de la tabla de la "hoja1", en otras celdas diferentes situadas en la "hoja2". No se si me explico, me lio yo mismo...jajaja, te pongo un ejemplo.

Lista dependiente1 en hoja 2:
TIPOS DE BUQUE:(aqui elijo ferry p.ej.)
Lista dependiente2 en hoja 2:
PESO: (aqui elijo 40.000 ton. p.ej.)
Y luego me gustaria k me diese los siguientes valores que vienen de la tabla de la cual tiran las 2 listas dependientes:
ESLORA:
MANGA:
y CALADO:
Necesito que me de estos 3 valores para luego asignarlos a unas formulas, y que me haga una serie de calculos.
Gracias de antemano por todo lo que ayudas en general, te hablo de que sin saber nada de excel ando metido ahora en esto para haber si lo saco adelante.
P.d.: Trabajo con el excel 2003 por si te sirve de algo.

Unknown dijo...

Hola. Tengo 2 dudas. Se pueden validar datos desde otro libro??
La otra pregunta es, como valdiar datos dependientes pero que sean dinámicos? es decir, si quisiera luego de hacer la la validción de tu ejemplo añadir mas datos a la lista sin que tenga que modifcar el nombre del grupo para agrandar el rango que este cubre. Se que para hacer una validacion simple(es decir indirecta) basta con utilizar esto. EJEMPLO =DESREF(hoja$B$4;0;0;CONTARA(hoja$B:$B);1)
pero si utilizo esto usando la funcion indirecta no me devuelve nada.

Saludos

Gracias!

Monchito dijo...

Excelente tu blog! pregunta: ¿existe forma de validar varias listas que dependen de una sola? Por ejemplo: 1ºlista Marcas de Autos, 2º lista Modelos, 3º lista, colores, 4º lista segmento, etc... todas dependerán de la primera según entiendo. ¿podrías iluminarme?
Gracias!
Adrián

Anónimo dijo...

Excelente blog, gracias por tu colaboración Javier Marco... es de mucha utilidad. Me gustaría saber si tienes otras páginas o blogs,ya me enlace al face... saludos...

Javier Marco dijo...

Pues tengo alguno más, pero no tienen relación con Excel. En la sección de "Webs interesantes" (a la izquierda, en el menú de navegación), verás un par sitios más que tengo, una web de cocina, y un blog de vehículos clásicos, concretamente. Aparte de esos, tengo algún otro blog más :-)

Anónimo dijo...

Muchas gracias amigo me sirvio bastabte se agradece

beyaco dijo...

Hola amigo, muchas gracias por el post, me fue de mucha utilidad, pero necesito consultarte una cosa, como lo puedes replicar para las celda de abajo???

Anónimo dijo...

Hola esta bueno el ejemplo funciona y muy interesente para validadar datos, pero en el caso mio mi lista dependiente es la conbinación de dos celdas (B15:C15), y aplicando el código para limpiar los datos de la celda dependiente, no me funciona.

JuanVen dijo...

Que tal Javier,

Hice algunos cambios a la macro que inicialmente propusiste. Digamos que la uso para una lista de personas a las que se les asignará una tarea en un departamento y en un área específica. Por ejemplo: a la persona UNO la asigno el área de "Finanzas" (Validación principal, en tu ejemplo las marcas de los autos), luego la ubico en "Contabilidad" (en tu ejemplo los modelos), finalmente le asigno la función "Limpiar Escritorios" y ésta es una validación nueva. A mí me funciona de las mil maravillas, sin embargo cuando hago un cambio en otra parte de la hoja, no en las validaciones, por ejemplo, borrar una celda, se queda pegado y el libro no responde y debo forzar el cierre. ¿Qué crees estoy omitiendo? ¿o qué está fallando?

Saludos,

Private Sub Worksheet_Change(ByVal Target As Range)
'Si hay errores, que continúe
On Error Resume Next
'Si cambiamos el dato de la celda en la que nos encontremos,
'limpiamos el segundo y el tercer deplegable
celda = ActiveCell.Address
If Target.Cells = Range(celda) Then
'eliminamos el contenido de la primera celda a la izquierda y luego la segunda
Range(celda).Offset(0, 1).ClearContents
Range(celda).Offset(0, 2).ClearContents
End If
End Sub

Mafe dijo...

Hola

Estoy tratando de crear un macro que me borre los de unas celdas la cuales esta vinculadas entre si, pero solo he logrado que me funcione en la primera primera linea y no en toda la columna, es decir tengo una tabla en excel con los datos de varias personas y se debe elegir el País (P), ya al hacer esta selección me sale un listados de todos los Departamento (Q) pertenecen a ese País y finamente me arroja un listado con los Municipio(O) que pertenecen a ese departamento.

El Municipio depende de la elección del Departamento y este de la elección del País.

Con la macro que tengo solo logro que borre los datos de la celda Q2 y O2 al seleccionar el país en la celda P2 o que borre los datos de la O2 si solo cambio el dato de la celda Q2 y necesito que me funcione en toda la columna Q y O para cada persona en el listado.

Pongo el macro que tengo, gracias

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("P2") Then
Range("Q2").Value = ""
Range("O2").Value = ""
End If

End Sub