sábado, 10 de abril de 2010

Validación de celdas anidadas y condicionadas.

En anteriores entradas hemos desarrollado diferentes formas de anidar nuestros desplegables, mediante la herramienta Validación de datos. En esta ocasión añadiremos una nueva visión del problema.

Tenemos un primer nivel en el que definimos ciertos 'Nombres', pero en el que encontramos un elemento del listado de nombres que necesariamente deberá desplegarnos los valores de otro listado ('Gastos'); en un segundo campo 'Descripción' de nuestra base de datos hemos incorporado unas Validaciones condicionadas al primer campo 'Nombres'.

Podemos ver la estructura de nuestra base de datos completa, así como los elementos posibles a desplegar:


Observad que como siempre hemos dado nombres a nuestros rangos:
Descripción =Hoja1!$F$3:$F$6
Gastos =Hoja1!$G$3:$G$7
Nombres =Hoja1!$E$3:$E$8


La idea entonces es aplicar la lista de valores 'Nombres' al campo de la misma denominación, para que cuando definamos la Validación en el campo 'Descripción' nos despliegue o bien los elementos del rango 'Descripción' o bien del rango 'Gastos' si la celda de 'Nombres' corresponde.

Configuramos entonces para las celdas del campo 'Nombres' de la base de datos su Validación:


y para las celdas del campo 'Descripción' de la base de datos:



La pequeña incorpporación que he incluido esta vez ha sido el SI condicional
=SI(A2="Gastos";INDIRECTO(A2);Descripción)
la lectura sería, cuando el valor desplegado de la celda del campo 'Nombres' de la base de datos sea 'Gastos', entonces a su vez desplegaremos en el campo 'Descripción' el listado de 'Gastos', en caso contrario el concepto 'Descripción'.

22 comentarios:

  1. Quiero usar dos listas de validación de datos a efectos de contabilidad. La primera incluye los posibles movimientos, por ejemplo: gasto de efectivo, gasto banco, ingreso de efectivo e ingreso banco. En la segunda columna quiero que la lista desplegable sea condicional en función de si la primera columna se refiere a ingresos o gastos con independencia de que sea de efectivo o bancario (tipos de ingresos o tipos de gastos).

    Hasta ahora empleaba una fórmula de la siguiente forma:

    =SI(O(K179=$C$6;K179=$C$7);$A$109:$A$116;

    SI(O(K179=$C$1;K179=$C$2);$A$123:$A$166;0))

    El problema es que al ir incluyendo categorías para la primera columna la fórmula se hace enorme y no entra en la casilla de validación de datos. 

    Necesitaría alguna formula que verificara que K179 es igual a algún valor de un rango para ahorrarme los fórmulas O de muchos parámetros... 

    ¿Alguna idea?

    Gracias.

    ResponderEliminar
  2. Hola AGP,
    la cosa iría por aplicar, en lugar de una búsqueda exacta de los conceptos de tipos de ingresos y gastos (gasto de efectivo, gasto banco, ingreso de efectivo e ingreso banco), aplicar una búsqueda aproximada, por ejemplo con la función HALLAR. En la segunda celda validada, que dependa del valor del primer dsplegable, por ejemplo en L179, añadiríamos una Validación de datos tipo lista con:
    =INDIRECTO(SI(ESERROR(HALLAR("*ingr*";K179));SI(HALLAR("*gast*";K179)>=1;"gasto");"ingreso"))
    previamente habríamos asignado a dos rangos los nombres de 'ingreso' y 'gasto' donde estarían los tipos de ingresos y tipos de gastos.
    Claro está, la primera lista con tipos de movimientos, siempre deberán tener el texto 'ingr' si es un ingreso, o 'gast' si fuera gasto.
    Espero te sirva, procuraré subir un post explicándolo en cuanto pueda.
    Slds

    ResponderEliminar
  3. Hola necesito saber como realizar lo siguiente:
    Utilizar una validación de datos para garantizar que no se pueda introducir una fecha de cancelación cuando aun existe una deuda pendiente. Cabe destacar que tengo la columna del precio, el monto pagado y la fecha de cancelacion.

    ResponderEliminar
    Respuestas
    1. Hola,
      tendrías que añadir una validación de datos personalizada, con una función que condicione la existencia de deuda viva.
      Selecciona la celda de Fecha cancelación y añade la validación personalizada con:
      =C2=D2
      siendo C2 la celda del total del importe, y
      D2 la celda de lo pagado.
      Saludos

      Eliminar
  4. hola tengo un archivo en el cual quiero usar la opción validación de datos en cada celda y también quiero validar que la suma no supere un monto

    b2 criterio de validación, permitir numero entero entre 1 y 15
    c2 criterio de validación, permitir numero entero entre 1 y 10
    e2 criterio de validación, permitir numero entero entre 1 y 15

    Adicional en f2 quiero colocar una suma(a2;e2) en la cual excel valide que dicha suma sea mayor a 1 y menor a 30, ya que no quiero que las persona que llene la evaluación coloquen la ponderación máxima.

    ResponderEliminar
    Respuestas
    1. Hola,
      en realidad tendrías que añadir la validación de la 'suma' en cada una de las celdas B2:E2, esto es, en esas celdas una validación personalizada.
      Por ejemplo en la celda B2 la fórmula:
      =Y(B2>=1;B2<=15;E2>=1;E2<=30)
      y de igual forma para C2 y D2

      Saludos

      Eliminar
  5. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  6. Yo estoy trabajando con nombres largos en mi primera lista, a los cuales les quite comas y cambié espacios por guines bajos,para la segunda lista mi fórmula en validación de datos dice: =INDIRECTO(SUSTITUIR(F2,” “,”_”)).
    Pero no encuentro la forma de ponerle una condicional que diga que si la celda F2 está vacia, esta se quede en blanco automaticamente.
    Ojalá me puedan ayudar, gracias

    ResponderEliminar
    Respuestas
    1. Hola Laura,
      ten presente que la Validación de celdas NO es una fórmula sobre la celda, lo que significa que la acción no es directa.
      La validación es una restricción de datos a introducir en una celda.
      Se podría referir a una celda sin datos, pero siempre tendrías que introducir el valor vacío (salvo que ya lo estuviera) manualmente...

      La solución a estos problemas se solventa con programación, donde si replicamos y efectuamos el condicional que necesitarías sin problemas.
      Pero es algo elaborado si no tienes conocimientos de VBA. La idea es trabajar con un evento de hoja _Change para aplicarle el valor vacío o algún valor del listado.

      Saludos

      Eliminar
  7. De casualidad tendrás un ejemplo que pueda tomar como referencia, lo que pasa es que no manejo programación.

    Muchas gracias, saludos

    ResponderEliminar
    Respuestas
    1. Lo siento :(
      Busca en la categoría de Macros... pero no recuerdo algo parecido

      Saludos

      Eliminar
    2. Estimado Ismael, estaba revisando la parte de macros.
      Ya tengo mi código:

      Private Sub worksheet_change(ByVal target As Range)
      Dim i As Integer
      Dim max As Integer
      Application.ScreenUpdating = False
      Application.EnableEvents = False
      On Error Resume Next
      max = Range("B10").End(xlUp).Row
      For i = 1 To max
      If Cells(i, 1) = "" Then
      Cells(i, 2).ClearContents
      End If
      Next i
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      End Sub

      Pero me funciona de columna a columna.
      Quiero decir que tengo la columan a y b.
      Cuando borro la celda a1, se borra la celda b1.

      Pero ahora mi dilema es, como hacerle para que cuando borre la celda a1, se borre la celda a2.

      Ojalá me puedas ayudar :)

      Eliminar
    3. Podría ser algo así:
      Private Sub Worksheet_Change(ByVal Target As Range)
      If Not Intersect(Target, Range("A:A")) Is Nothing Then
      Cells(Target.Row, "B").ClearContents
      End If
      End Sub

      Saludos

      Eliminar
  8. Hola, estoy trabajando en una base de datos en la cual tengo 3 columnas que dependerán de la elección de la primera, es decir:

    1 columna: nombre de la comunidad autónoma
    2 columna: quiero que solo me permita elegir las provincias de la comunidad autónoma elegida
    3 columna: dependiendo de la comunidad y la provincia elegida que se pueda elegir un municipio.

    Me podrían ayudar porfis.
    gracias

    ResponderEliminar
    Respuestas
    1. Hola!
      es algo laborioso... pero no muy complejo
      Sigue los pasos de este ejemplo
      http://excelforo.blogspot.com/2009/10/ejemplo-de-doble-validacion.html

      El truco es el uso de la función INDIRECTO dentro de la configuración de la validación de datos

      Espero te oriente
      Saludos

      Eliminar
  9. Buenos dias Ismael, Muy educativa, util y practica la información que suministras por es via.

    Tengo una duda con respecto a la validación personalizada y creo que pudieras ayudarme, te explico; tengo en la Hoja 1 una tabla de tres columnas (columna A Departamento, Columna B material, Columna C codigo del material) los departamentos se repiten cada vez que se ingresa un material (son fijos o constantes y se conocen), quisiera entonces en la Hoja 2 generar dos validaciones una en donde se podrá seleccionar el departamento (esa esta resuelta) y la otra es que una vez que seleccione el departamento solo se muestren los materiales de este departamento.

    Intenté hacerlo por medio de la función indirecto pero se me hizo imposible ya que no se tiene una lista definida de todos los materiales que se pudieran ingresar en la tabla de la Hoja 1 para con esta lista generar la tabla y asignarle el nombre al rango y asi suscesivamente los pasos necesarios para utilizar la validación con la función indirecto.

    Entonces como puedo hacer para generar esta segunda validación??? te agradeceria la ayuda

    ResponderEliminar
    Respuestas
    1. Hola,
      no sería posible directamente (que quedará bien estéticamente, sin espacios vacíos/en blanco entremedias)... por lo que deberás hacer una tabla auxiliar donde se liste mediante fórmulas los 'materiales' asociados a tu elección de 'Departamento'.
      En el blog hay publicados algunos ejemplos de cómo listar elementos...

      Un saludo!

      Eliminar
    2. Saludos Ismael, gracias por responder, precisamente allí es donde estoy estancado, como hago que esa tabla auxiliar se actualice automáticamente y a su vez se actualice el rango al que le daría el respectivo nombre que posteriormente utilizaría con la función indirecto?

      Te agradezco el apoyo!!

      Eliminar
    3. Hola!
      publicaré una entrada en el blog al respecto...

      Un saludo!

      Eliminar
    4. recordé ya había publicado algo muy similar
      https://excelforo.blogspot.com/2015/04/una-validacion-de-datos-por-aproximacion.html

      Con una simple macro construidas bajo eventos _Change en la hoja podrás conseguir lo que quieres.

      Espero te oriente
      Slds

      Eliminar
    5. Excelente!!!, intentaré con esta solución y te aviso si se lograron los resulatdos deseados.

      Gracias por tu tiempo.
      Slds

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.