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:

Validación de celdas anidadas y condicionadas.


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:

Validación de celdas anidadas y condicionadas.


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

Validación de celdas anidadas y condicionadas.



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'.

13 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