miércoles, 29 de junio de 2011

HIPERVINCULO asociado a un desplegable de celda validada.

Aprenderemos hoy a generar un listado de hipervínculos mediante funciones que nos dirija a cada una de las hojas de nuestro libro de trabajo desplegados mediante una Celda validada tipo lista:

...Tengo 1 archivo Excel con 14 hojas.
Quiero que desde cualquiera de esas hojas donde me encuentre (ejemplo: Hoja 3) pueda acceder a cualquier otra Hoja, cuyos nombres los tengo en una lista de validación: selecciono el nombre de la hoja a la que quiero ir y debería acceder a la misma mediante hipervínculo.
En cada una de esas hojas puse una celda con validación, listas, desde donde puede seleccionar el nombre de cualquiera de las hojas restantes del Excel.....


Tiempo atrás trabajamos con la herramienta HIPERVINCULO (ver), pero en esta ocasión deberemos emplear la función:
=HIPERVINCULO(ubicación_enlace; [nombre_enlace])
Lo ideal sería, para responder la cuestión planteada, poder combinar esta función hipervínculo dentro de los elementos a desplegar con una Celda validada tipo lista... pero como con el estándar de Excel esto no es posible (al menos yo no lo he logrado), anexaremos una función a los valores mostrado de una celda validada.

Nuestro trabajo comienza entonces construyendo una Celda con validación tipo lista, que nos muestre los diferentes nombres de las hojas de nuestro libro de trabajo.
En nuestro pequeño ejemplo, supondremos un libro de trabajo con tres hojas Hoja1, Hoja2 y Hoja3.


Celda que situaremos en la celda A1 de las tres hojas del libro:


En siguiente paso es asignar en la celda B1 de cada hoja la siguiente función:
=HIPERVINCULO(("#"&A1&"!A1");A1&"!A1")
o bien
=HIPERVINCULO(("[Libro3]"&A1&"!A1");A1&"!A1")
es importante fijarse en la forma de construir la ubicación del vínculo, que conseguimos concatenando en primer lugar con la almohadilla # o bien con el nombre del libro de trabajo entre corchetes [Libro3]; seguido del valor obtenido mediante el desplegable de validación de datos y por último de la celda destino donde deseemos que nos dirija nuestro hipervínculo (en nuestro ejemplo a la celda A1 de cada hoja de cálculo):
"#"&A1&"!A1"
ó
"[Libro3]"&A1&"!A1"


Debemos tener cuidado con la construcción de nuestra ubicación del vínculo, ya que es la parte de la función que nos dirigirá al destino requerido!!
El segundo argumento de la función HIPERVINCULO sirve para mostrar un valor en la celda con el hipervínculo, yo me he decantado por un valor sencillo, que muestre la Hoja y la celda donde nos llevará.

Con esto finalizaríamos nuestra tarea, consiguiendo poder desplazarnos por todas las hojas de nuestro libro de trabajo mediante hipervínculos generados con la función HIPERVINCULO, en función al valor desplegado de un celda validada tipo lista.

29 comentarios:

  1. Me ha parecido de suma utilidad, y te agradezco la predisposición a compartir tus conocimientos. además de efectivo eficiente ya que una consulta que te hice hace pocos días se ha resuelto a la brevedad. Muchas gracias. Leo de Santa Fe, Argentina

    ResponderEliminar
  2. muy buena informacion, gracias , gracias ha personas como tu muchos como yo podemos trabajar de una forma mejor. adelante y que tengas mucha suerte y salut..

    ResponderEliminar
    Respuestas
    1. Muchas gracias por vuestras palabras de apoyo...
      que siga habiendo personas interesadas en lo que escribo es lo que me anima a seguir!!
      Slds cordiales

      Eliminar
  3. Hola,

    Junto con agradecer que compartas tus conocimientos de Excel con todos nosotros, requiero tu ayuda, por favorrrr!!!

    Tengo en la hoja 1, tengo el rango J13:U22. En la columna J, tengo codigos de productos; En la columna U quiero poner un "buscarv" que valla a buscar a la hoja 2, el codigo ingresado en la columna J y segun ese codigo mostrar un hipervinculo a la ficha del producto.

    Desde ya agradezco tu ayuda..



    jajaj

    ResponderEliminar
    Respuestas
    1. Hola,
      bueno, la primera parte parece que está clara, emplear un BUSCARV... para el tema del Hipervínculo tendrás que anidar el BUSCARV en una función HIPERVINCULO.
      Por ejemplo, suponiendo que la tabla origen de Cod y Descrp están en la hoja 2 en el rango A:B, entonces en la hoja 1, en la celda U13 escribiríamos algo de este estilo:
      =HIPERVINCULO(BUSCARV(J6;$A$1:$B$2;2;0);BUSCARV(J6;$A$1:$B$2;2;0))

      lógicamente habrá que depurar dónde dirigir el hipervínculo!!!!.
      Si me adjuntas un ejemplo a
      excelforo@gmail.com

      lo reviso y te comento más concretamente.
      Slds

      Eliminar
  4. te agardazco por compartir muchas cosas novedosas ..me haz sacado de apuros en varias ocasiones.GRACIAS.
    una consulta tengo en mente en hacer algo pero hasta ahora nos e como hacerlo quiero que por medio de una validacion de datos me abra archivos de word..no se si me entienda que al escoger de esa lista de fatos me abra un word

    ResponderEliminar
    Respuestas
    1. Hola Dennis,
      tendrás que seguir las indicaciones de este mismo post, y en la función hipervínculo añadir en la parte de la ruta, el path completo (nombre del fichero incluido).. da igual que sea u Word o cualquier otro tipo de fichero.
      Por ejemplo
      =HIPERVINCULO("E:\Mis documentos\Excelforo\Excelforo.docx";"abre Word")

      Espero te sirva
      Slds

      Eliminar
  5. buenos Dias tengo un percance estoy haciendo un libro de productos que para ir a un producto determinado, se inici con listas desplegables, que al final la ultima seleccion envia un hipervinculo que habre toda informacion del producto final deleccionado, pero siempre me da error o no envia al sitio que deseo, lo invito a ver el archivo en...

    ResponderEliminar
  6. en
    https://docs.google.com/file/d/0Bw7i8yy-CiuKVG9zUnV0U3NfOWs/edit?usp=drive_web

    ResponderEliminar
  7. Hola
    Después de dar vueltas por el foro tengo un problema que no he podido/sabido resolver:
    Tengo una hoja1 con la función =HIPERVINCULO("#hoja2!D2"; "Info") en la celda K2 en la que al pichar me lleva a una celda con más de 255 caracteres de texto. Al copiar la fórmula en las celdas inferiores me lleva siempre a la celda D2 y quisiera que se actualizara a la celda D3, D4...
    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Manolo,
      es lógico que al copiar la fórmula te lleve siempre a la celda D2 puesto que esa es la celda que tienes indicada, para que según copies/arrastres hacia abajo cambie tu fórmula y la dirección del vínculo tendrías que hacer en K2:
      =HIPERVINCULO("#hoja2!D" & FILA(); "Info")

      Saludos

      Eliminar
    2. Funciona!
      Muchas gracias por tu respuesta.
      Estoy aprendiendo y resolviendo muchas dudas con este foro pero con esta no podía.

      Eliminar
    3. ;-)
      Gracias.. un cordial saludo!!

      Eliminar
  8. buen noche disculpa me podrias ayudar, tengo una hoja de calculo en donde tengo todas las fechas del mes, pero una en cada celda, siempre utilizando la misma hubicacion po rejemplo, D5, E5, F5, etc. y lo que quiero que al poner en una celda la fecha del dia x, me aparezca un vinculo que me dirija hacia la celda en donde esta escrita esa fecha, de antemano mu chas gracias

    ResponderEliminar
    Respuestas
    1. Hola Wily,
      supongamos la ubicación de las fechas del mes de enero en las celdas A3:A33, y desplegamos la fecha x en la celda C1, entonces en D1 insertas:
      =HIPERVINCULO("#"&DIRECCION(2+COINCIDIR(C1;$A$3:$A$33;0);1);"ir fecha")

      Como ves se necesita el vínculo en la celda de al lado para hacerlo con fórmulas.. de igual manera que lo explicado en este mismo post.
      Otra solución sería aplicar macros.
      un saludo

      Eliminar
    2. Gracias mi amigo, la formula esta fabulosa, únicamente seria posible que buscara la fecha pero que no este en una misma columna, ejemplo de la columna D a la columna Z ? gracias

      Eliminar
  9. Se me olvidaba las columnas siempre mantienen en la fila 5, ejemplo D5, E5, F5, G5,. La verdad me gustaria el hipervinculo y no una macro, bueno si fuera posible. gracias. y felicitaciones por el foro, es de mucha utilidad

    ResponderEliminar
    Respuestas
    1. Suponiendo comienzan las fechas en D5, E5, etc...
      la fórmula sería parecida, suponiendo en C1 donde despliegas la fecha
      =HIPERVINCULO("#"&DIRECCION(5;3+COINCIDIR(C1;$D$5:$I$5;0));"ir fecha")

      Espero haber comprendido bien

      Saludos

      Eliminar
    2. Sinceramente, te agradezco. muy satisfecho.gracias. y bendiciones

      Eliminar
  10. BUENAS NOCHES!!! QUISIERA HACER ESTE MISMO HIPERVINCULO CONDICIONAL CON UN BOTON DE MACROS Y ADEMAS DE ESO A LA HOJA Q ME DIRECCIONA EL VINCULO SE LLEVE ALGUNOS DATOS DE LA HOJA ORIGINAL. DE ANTEMANO MUCHAS GRACIAS

    ResponderEliminar
  11. muchas gracias fue de gran ayuda en mi trabajo ya que trabajo con decenas de hojas en excel muchas gracias excelente aporte.

    ResponderEliminar
  12. Tengo un libro excel donde existe una primera hoja que en diferentes celdas escoge manualmente su valor de otra hoja, ese valor tiene asociada una tarea y un codigo, quisiera enuna nueva hoja añadida obtener un resumen de todas las tareas escogidas en la primera, podrias darme una pista de como poder hacerlo, ando loco probando formulas y mcros y nada de nada..... gracias

    ResponderEliminar
    Respuestas
    1. Hola Fco Javier,
      no me queda claro qué necesitas...parece lo que necesitas es listar las tareas seleccionadas en la primera hoja (y asociadas a otras hojas). La pregunta es si bastaría listar en un rango de celdas los códigos elegidos o necesita algo más, como el nombre de la hoja, etc...
      Si quieres mejor envíame a
      excelforo@gmail.com
      un ejemplo y explicación del asunto.
      Un saludo

      Eliminar
  13. Muy buenas,

    Quiero hacer este mismo ejercicio, pero que me abra un nuevo archivo de excel llamado "EXCELFORO.xls" y almacenado en C:/Mis Documentos.

    ¿Como seria el codigo?

    ResponderEliminar
    Respuestas
    1. De echo, la información de la ubicación "C:/Mis Documentos" está escrita en una celda del libro original.

      Eliminar
    2. Hola Rafael,
      la función tendría esta forma:
      =HIPERVINCULO("C:\Mis documentos\Excelforo.xls";"abre fichero")

      o si como indicas tienes parte de la ruta en una celda, por ejemplo A1:
      =HIPERVINCULO(A1&"\Excelforo.xls";"abre fichero")

      Saludos

      Eliminar

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