24 de febrero de 2009

Validación de datos dependiente

Como es sabido, la validación de datos nos permite establecer reglas que determinan lo que puede y lo que no puede ser ingresado en una celda. Podemos especificar un mensaje de entrada y un mensaje de error (y el tipo de este mensaje, es decir, de información, de advertencia o de límite).

Un usuario me envía la siguiente pregunta: "... lo que necesito hacer es que de acuerdo a la selección que hagan de un campo, en el siguiente solo me den las opciones referentes a ese campo y no todas,... un ejemplo es cuando abres una cuenta de correo y te piden tu país, le das "México" y en el siguiente campo te aparecen solo los estados de México..."

Desde luego podemos intentar con un par de controles y un código VBA más o menos sencillo aunque, en realidad, es posible lograrlo sin necesidad de utilizar macros. Al igual que sucede con el formato condicional, al utilizar criterios personalizados (formulados), la validación de datos se vuelve una herramienta muy potente.

El primer paso es organizar nuestros datos. En la primera columna ponemos los valores independientes y, a la derecha, los dependientes. Es necesario que la lista esté ordenada por la primera columna. En otra columna, pongamos la D, escribimos una lista de los elemento únicos de la primer columna:

Por comodidad definimos los siguientes nombres:

La celda A1 con el nombre "inicio"; la columna A con el nombre "independiente"; la columna B como "dependiente" y la lista de la columna D como "lista". Opcionalmente, en otra celda en blanco, escribimos "Seleccione un valor en la columna A" y la definimos con el nombre "mensaje_error"

En otra hoja, creamos un tabla sencilla para crear las listas de validación:


Seleccionamos el rango A2:A10 y vamos a Datos - Validación... Como valor Permitir seleccionamos Lista. En el campo Origen escribimos:

=SI(O(B2="",B2="Seleccione un Ramo"), lista, INDICE(independiente, COINCIDIR(B2, dependiente, 0)))


Esto sirve para que, si no hay ningún valor en la columna B, o bien, el valor en la columna B sea "Seleccione un ramo", en la lista de la columna A aparezcan todos los valores. Por el contrario, si ya tenemos un valor establecido en la columna B (la dependiente), en la lista de validación de la columna A solo aparecerá su correspondiente valor, y no todos.

Solo nos falta crear la validación de la columna B. Seleccionamos el rango B2:B10, vamos a Datos - Validación... en Permitir seleccionamos Lista y, en Origen, escribimos la fórmula:

=SI(A2="",mensaje_error,DESREF(inicio,COINCIDIR(A2,independiente,0)-1,1,
CONTAR.SI(independiente,A2),1))

Con esta fórmula, si el usuario pretende seleccionar un valor en la columna B, sin haber seleccionado primero el correspondiente valor de la columna A, la lista solo mostrará la opción "seleccione un valor en la columna A".

De otra forma, la lista mostrará los valores adecuados.

8 comentarios:

  1. Hola,

    Estoy encantado con esta forma de crear listas dependientes, pero, estoy intentando realizar 4 niveles y me estoy volviendo loco...

    Alguien me echa un cable?
    Gracias de antemano,

    Un saludo,

    ResponderEliminar
  2. Me ha sido de gran ayuda, mil gracias!

    ResponderEliminar
  3. Excelente compañero, solo una pregunta, veo que efectivamente se establece la dependencia entre las listas de validacion, pero, ¿como le hago para que no permita la captura de valores invalidos que no figuren en las listas?, porque en el ejemplo si deja hacerlo. Gracias!

    ResponderEliminar
  4. holas, quisiera me ayudaran en los siguente:
    quisiera validar datos en una columna de excel que es resultado de una fórmula( a1+b1)= -30
    puedo validar igresando direcramente el dato, no asi con el resultado de ésta formula, gracias
    si me puenden enviar la respuesta en jramirez@ninoshka.com
    gracias

    ResponderEliminar
  5. esto a mi no me funcionaaaaaaaaaaaaaaaaaaa

    ResponderEliminar
  6. GRACIAS!!!! MUY DIDÁCTICA LA EXPLICACIÓN!!!

    ResponderEliminar
  7. ortega compra las trufas

    ResponderEliminar

Creative Commons License
Esta obra está bajo una licencia de Creative Commons.
Excel, Excel 2003, Excel 2007 y el logo XL son marcas registradas de Microsoft Corporation.