domingo, 28 de octubre de 2012

Mejora de listas desplegables con la función INDIRECTO



Hace un par de semanas, un gran amigo peruano, a quien estimo mucho, pues es profesor y capacitador también, una persona con mucho conocimiento, pero sobre todo con la humildad de querer seguir aprendiendo mas y mas; me consulto por correo electrónico  si conocía la forma de que al seleccionar un dato de una lista, se limpie la celda de la lista relacionada...

En otras palabras, para explicar mejor esta inquietud, vamos inicialmente a trabajar con una Hoja de datos personales, en la cual si selecciono un país, pueda seleccionar la lista de ciudades que pertenecen a este país...

Aunque ya hemos visto Listas desplegables con la función indirecto, vamos a darle una repasada a este tema...


En otra hoja colocaremos los países y sus ciudades correspondientes de la siguiente manera:



Además, debemos definir los nombres de las listas (Etiqueta Fórmulas / Administrador de nombres).

Una vez definido los cinco nombres regresamos a la hoja donde se encuentra la Hoja de datos personales.

Seleccionamos la celda C5 (es decir, la que corresponde a País de Nacimiento) y nos vamos a la etiqueta Datos, seleccionamos Validación de datos



En cuadro de Validación de datos, debemos colocar que se debe permitir Lista; y en origen colocamos el nombre de la lista con un igual, ejemplo: =paises



En la celda C7, que corresponde a la Ciudad, debemos realizar el mismo procedimiento con la función INDIRECTO...
            =INDIRECTO($C$5)

De esta forma, cada vez que seleccione un País, automáticamente la lista de ciudades cambia. 

Sin embargo, si yo selecciono por ejemplo como país Perú, luego selecciono la ciudad de Lima; si cambio de país; la lista se actualiza, pero la celda se quedo con el valor de Lima, y esto generaría errores...

Para poder limpiar la ciudad, cada vez que cambie el país, debemos entrar al Visual Basic, y para ello haremos clic derecho en la etiqueta de la hoja (parte inferior de la pantalla) y seleccionaremos la opción Ver código...

Estando ya en Visual Basic, escribimos el siguiente código:

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


Cerramos el Visual Basic, y cada vez que cambiemos de País, la celda con la ciudad se limpia automáticamente.

Cualquier consulta me la pueden indicar por este medio.

6 comentarios:

  1. Hola buenos días, le estoy muy agradecido por sus aportaciones ya que llevo dias aprendiendo con esta pagina pero me encunetro con un problema que no termino de resolver. Cómo seria la modificación de dicho codigo que comentas para que me sirva no solo para una celda concreta sino para todo un rango, es decir para no tener que copiar el codigo para cada celda que requiera de dicha actualización. Gracias de antemano.

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

    ResponderBorrar
    Respuestas
    1. Hola, Gracias por tu comentario, y disculpa que no te contestara antes. Respecto a tu pregunta, podrias realizar las siguientes modificaciones al codigo:

      Para limpiar una tabla:

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Union(Range("C5"), Target).Address = Range("C5").Address Then
      Range("D2:E4").ClearContents
      End If
      End Sub

      Para limpiar rangos de celdas que no estan continuas:

      Private Sub Worksheet_Change(ByVal Target As Range)
      If Union(Range("C5"), Target).Address = Range("C5").Address Then
      Range("D2:D4,D7:D9,D12:D15").ClearContents
      End If
      End Sub

      Cualquier otra consulta me la indicas.

      Saludos

      Borrar
    2. Hola Kike;
      Mejor gracias a ty por contestar y ayudarnos a resolver problemas. Es justo lo que andaba buscando.
      Muchas gracias por tu ayuda, sigue así.

      Borrar
    3. Con mucho gusto, estoy para servirte

      Borrar
  2. Querido profe, trato de desplegar el click derecho para tener la opción "ver código" pero no me da la opción, traté de buscar otra opción en el teclado pero no la encuentro. Gracias.

    ResponderBorrar
    Respuestas
    1. Buenos días, eso se debe porque lo estabas abriendo desde el navegador online. Debes descargar el archivo y abrirlo desde el propio Excel.

      Borrar