Hace algún tiempo escribí una entrada donde explicaba un ejemplo empleando la función HIPERVINCULO (ver).
Pues días atrás se me planteaba un nuevo ejemplo, algo diferente, en el que se hacía necesario emplear de nuevo esta función. En este caso la dificultad radica en no sólo construir el hipervínculo, si no también identificar la ubicación o destino de ese hipervínculo.
La cuestión planteada por un lector fue:
La idea por tanto es clara, construir un hipervínculo 'dinámico' asociado al valor de una celda.
Partiremos de una tabla origen (Tabla1) que podrá estar en cualquier hoja de nuestro Libro, en mi ejemplo, la veremos en la misma hoja (Hoja1) donde analizaremos nuestra función; en esta Tabla1 encontraremos un listado de productos, que es sobre lo que queremos recaiga el HIPERVINCULO una vez construida nuestra fórmula.
Por otro lado tenemos una segunda tabla (Tabla2) sobre la cual generaremos una fórmula que nos dirija (mediante el hipervínculo) al producto concreto en la Tabla1.
Veamos la disposición de datos:
El fin es generar una fórmula en la Tabla2, que nos dirija, mediante hipervínculos a la Tabla1, pero al elemento concreto. La fórmula a añadir en B2:B5 es:
=HIPERVINCULO("#"&"Hoja1!"&DIRECCION(COINCIDIR(A5;Tabla1[Cod];0)+1;COLUMNA(Tabla1);1;1);BUSCARV(A5;Tabla1;2;0))
Procedemos a desglosarla y explicarla.
En primer lugar la función HIPERVINCULO(ubicación; descripción) tiene dos argumentos.
El primero de ellos, por sencillez sería el argumento descripción que es el que muestra el texto del hipervínculo; en nuestro ejemplo he optado por mostrar la Descripción del producto indicado:
BUSCARV(A5;Tabla1;2;0)
con esta fórmula de BUSCARV conseguimos el valor correspondiente a la segunda columna de la Tabla1, es decir, el valor del campo 'Producto'. Es un simple texto, que podríamos cambiar por cualquier otro.
Donde está toda la funcionalidad es en el primer argumento de la función HIPERVINCULO, el argumento Ubicación; en él radica la dirección donde queremos nos lleve nuestro hipervínculo. La fórmula sería:
"#"&"Hoja1!"&DIRECCION(COINCIDIR(A5;Tabla1[Cod];0)+1;COLUMNA(Tabla1);1;1)
Vemos que comenzamos concatenando una almohadilla # al nombre de la hoja destino (en mi ejemplo Hoja1, si la Tabla1 estuviera en la Hoja2, aquí se cambiaría). Recordar que es necesario esa almohadilla o indicar el nombre del Libro entre corchetes!!.
A continuación lo unimos a la función DIRECCION.
Esta función DIRECCION (ya vista en otras entradas del blog) requiere como argumentos la fila y columna de la celda en cuestión, además de un tercer argumento que indicaría el tipo de referencia (absoluta, relativa o mixta), así como el cuarto argumento con el que indicaremos el estilo F1C1 o A1.
Lo interesante de esta función DIRECCION es cómo hemos logrado el número de fila y columna que identifica la celda destino dentro de la Tabla1. La fila la logramos con la función COINCIDIR:
COINCIDIR(A5;Tabla1[Cod];0)+1
que busca en el campo 'Cod' de la Tabla1 la coincidencia del valor buscado. Sumamos +1 para saltar la cabecera de la Tabla.
La columna se localiza con la función COLUMNA aplicada a la Tabla1, lo que nos dirá la posición de la primera columna donde comience la Tabla1:
COLUMNA(Tabla1)
Todo junto nos da el siguiente aspecto, mostrando una celda con una descripción de producto correspondiente al Cod buscado, y sobre todo con la funcionalidad que nos dirije a la celda en cuestión de la Tabla1:
Pues días atrás se me planteaba un nuevo ejemplo, algo diferente, en el que se hacía necesario emplear de nuevo esta función. En este caso la dificultad radica en no sólo construir el hipervínculo, si no también identificar la ubicación o destino de ese hipervínculo.
La cuestión planteada por un lector fue:
| ...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... |
La idea por tanto es clara, construir un hipervínculo 'dinámico' asociado al valor de una celda.
Partiremos de una tabla origen (Tabla1) que podrá estar en cualquier hoja de nuestro Libro, en mi ejemplo, la veremos en la misma hoja (Hoja1) donde analizaremos nuestra función; en esta Tabla1 encontraremos un listado de productos, que es sobre lo que queremos recaiga el HIPERVINCULO una vez construida nuestra fórmula.
Por otro lado tenemos una segunda tabla (Tabla2) sobre la cual generaremos una fórmula que nos dirija (mediante el hipervínculo) al producto concreto en la Tabla1.
Veamos la disposición de datos:
El fin es generar una fórmula en la Tabla2, que nos dirija, mediante hipervínculos a la Tabla1, pero al elemento concreto. La fórmula a añadir en B2:B5 es:
=HIPERVINCULO("#"&"Hoja1!"&DIRECCION(COINCIDIR(A5;Tabla1[Cod];0)+1;COLUMNA(Tabla1);1;1);BUSCARV(A5;Tabla1;2;0))
Procedemos a desglosarla y explicarla.
En primer lugar la función HIPERVINCULO(ubicación; descripción) tiene dos argumentos.
El primero de ellos, por sencillez sería el argumento descripción que es el que muestra el texto del hipervínculo; en nuestro ejemplo he optado por mostrar la Descripción del producto indicado:
BUSCARV(A5;Tabla1;2;0)
con esta fórmula de BUSCARV conseguimos el valor correspondiente a la segunda columna de la Tabla1, es decir, el valor del campo 'Producto'. Es un simple texto, que podríamos cambiar por cualquier otro.
Donde está toda la funcionalidad es en el primer argumento de la función HIPERVINCULO, el argumento Ubicación; en él radica la dirección donde queremos nos lleve nuestro hipervínculo. La fórmula sería:
"#"&"Hoja1!"&DIRECCION(COINCIDIR(A5;Tabla1[Cod];0)+1;COLUMNA(Tabla1);1;1)
Vemos que comenzamos concatenando una almohadilla # al nombre de la hoja destino (en mi ejemplo Hoja1, si la Tabla1 estuviera en la Hoja2, aquí se cambiaría). Recordar que es necesario esa almohadilla o indicar el nombre del Libro entre corchetes!!.
A continuación lo unimos a la función DIRECCION.
Esta función DIRECCION (ya vista en otras entradas del blog) requiere como argumentos la fila y columna de la celda en cuestión, además de un tercer argumento que indicaría el tipo de referencia (absoluta, relativa o mixta), así como el cuarto argumento con el que indicaremos el estilo F1C1 o A1.
Lo interesante de esta función DIRECCION es cómo hemos logrado el número de fila y columna que identifica la celda destino dentro de la Tabla1. La fila la logramos con la función COINCIDIR:
COINCIDIR(A5;Tabla1[Cod];0)+1
que busca en el campo 'Cod' de la Tabla1 la coincidencia del valor buscado. Sumamos +1 para saltar la cabecera de la Tabla.
La columna se localiza con la función COLUMNA aplicada a la Tabla1, lo que nos dirá la posición de la primera columna donde comience la Tabla1:
COLUMNA(Tabla1)
Todo junto nos da el siguiente aspecto, mostrando una celda con una descripción de producto correspondiente al Cod buscado, y sobre todo con la funcionalidad que nos dirije a la celda en cuestión de la Tabla1:
Hola, Ismael
ResponderEliminarqueria hacerte una consulta, tratare de explicarlo un poco...
Tengo por ejemplo 3 planillas de excel, 2 con fechas de tareas y 1 resumen de cada fecha que se obtiene solo para visualizar.
En estas 2 planillas con datos tengo unas fechas definidas con la funcion [HOY()] cosa que cuando se llegue a la fecha actual las fechas antiguas pasadas me marquen con el numero [1] las vencidas y [0] las que no han vencido.
En el resumen utilizo la funcion [=consultaV] y reviso que tareas estan vencidas...
mi tema es que a veces llegadas a la fecha me aparece como que la tarea no esta vencida y cuando abro el archivo de origen recien se me actualiza la planilla del resumen
hay alguna manera de que se actualice el resumen sin tener que abrir el origen (que se actualice automaticamente la planilla de origen) y que cambie automaticamente en mi resumen ??? ya que al no abrir el origen (no sé si por tener fechas estas no se me actualizan automaticamente) no me cambian los datos del resumen.
Gracias
Hola Nicole,
Eliminarlo más sencillo, sin emplear Macros no abrir el origen, sería acceder al Editor de vínculos y presionar el botón de Actualizar valores...
Ahora bien, esta herramienta de Excel, en ocasiones no funciona como quisieramos.
Otras opciones pasarían por emplear alguna macro que actualice esos vínculos. Puedes leer al respecto en
http://excelforo.blogspot.com.es/2012/10/vba-updatelink-actualizar-vinculos-en.html
Slds cordiales
Gracias Ismael, pero no me funciona, probé de las 2 formas y no se actualizan automaticamente asi que tendre que hacerlo manual, de todas formas gracias por tu ayuda.
EliminarLo siento Nicole..
Eliminarcomo te comentaba esta herramienta muchas veces no funciona como esperamos, y nos obliga a abrir los orígenes de datos.
Slds
Hola Ismael, lo primero agradecerto lo bien que me ha venido en infinidad de ocasiones tus tutoriales.
ResponderEliminarLa formula que planteas en este post es justo la que necesito, pero con una salvedad, y es que yo en la tabla1, no tendría encabezados de columnas, es decir, tengo un rango de numeros y me gustaría que en funcion del dato de una celda, me llevara por hipervinculo a ese mismo dato en mi tabla de numeros que en mi caso , se encuentra en otra pestaña. Mi formula sería la siguiente:
HIPERVINCULO("#"&"'"&T(L2)&"'"&"!d48";(A2&-B2))
En lugar de poner D48, que es donde se encuentra la celda con el numero 1, que es el valor que tengo como buscar en mi hoja de hipervinculo, exactamente en la celda B2.
He probad con funciones como Dirección, indice... pero no lo consigo
No sé si sera posible...
Muchas gracias por tu ayuda, Susana
No me he explicado bien, En lugar D48 en la formula, me gustaría que la formula buscase el valor de la celda b2, en una tabla con valores, entre los que se encuentra dicho valor.
Eliminarespero haberme explicado mejor
Hola Susana,
Eliminarla función HIPERVINCULO tiene como primer argumento la ubicación o destino de link...
Me parece entender que necesitas que el destino sea dinámico, que en lugar de D48 sea B2 u otra celda según algún criterio...
Si es así,lo primero es ser capaz de replicar la regla que retorna esa celda B2.
Las funciones que se suelen emplear son las que comentas DIRECCION o INDICE...
Te recomendaría aclararas muy bien como llegar a esa celda B2 de destino, y así poder especifica o replicar una fórmula que sea capaz de tal cosa.
Saludos
Hola Ismael,
ResponderEliminarHe probado con la formula que indicas pero no me funciona porqué mi caso es un poco distinto al que propones. Te explico.
Necesito que un hipervinculo de "Hoja1" del libro me lleve un texto exacto (sin importar la celda, porqué cambiará de sitio a menudo) de la "Hoja2".
He probado con:
=HIPERVINCULO(COINCIDIR("DUBAI-M-P";'Switches Y Routers'!F:F;0);"Switches y Routers")
Donde "DUBAI-M-P" es el texto exacto a Buscar en la columna F de la Hoja "Switches y Routers". Ver que como descripción del hipervinculo solo quiero un texto (eso si que funciona).
NOTA: ese texto exacto solo se va a encontrar en la columna F.
Esta formula me da como error que no encuentra el fichero especificado, cosa que me indica que no le he indicado bien que busque en una hoja del mismo libro.
A ver si puedes corregir mi error porqué me he dado de cabeza ya muchas horas con esto...
Mil gracias y saludos.
Hola Gus,
Eliminarla idea es emplear una dirección como primer argumento de la función HIPERVINCULO.. al usar COINCIDIR solo obtienes un número (NO una dirección), por tanto se trata que en lugar de ese coincidir emplees otra combinación de funciones que te devuelva una dirección de una celda.
Echa un vistazo a estos link, te ayudarán a montar tu fórmula:
http://excelforo.blogspot.com.es/2014/11/busqueda-doble-sobre-cualquier-parte-de.html
o también
http://excelforo.blogspot.com.es/2014/12/vba-busqueda-doble-sobre-cualquier.html
Saludos
Hola, Tengo un problema con la función hipervínculo. En una tabla tengo documentos de personas con sus respectivos mails. En otra tabla a partir de la introducción del documento, utilizando la función BUSCARV() hago que me traiga el mail pero se pierde el hipervínculo generado en la primer tabla. Probe con la función HIPERVINCULO(BUSCARV()) pero tampoco funciono, alguno tiene una idea?
ResponderEliminarHola,
Eliminarpues debe funcionarte (he replicado el caso), la función HIPERVINCULO opera con dos argumentos, el primero para la ubicación (email, URL, etc) y el segundo (opcional) para desplegar el texto...
Si con BUSCARV te traes una dirección, HIPERVICUNLO tiene que convertirlo... si no es el caso, quizá el problema esté en BUSCARV...
Saludos
Hola, muchas gracias por tu rápida respuesta. Hoy probare nuevamente a ver que sucede. Gracias
EliminarHola. He construído una URL concatenando valores de diversas celdas para que me devuelva el valor de la celda a la que apunta la URL, pero me muestra la ruta y no el valor. La única solución que he encontrado es copiar la ruta, pegarla como valor en otra celda y, antes de pulsar ENTER, pulsar escape y F2... pero es un procedimiento muy pedestre. ¿Cómo puedo obtener directamente el valor?
ResponderEliminarHola Diego,
Eliminarsobre esa ruta compuesta aplícale la función INDIRECTO...
DE todas formas, me imagino no será una URL (de una web) será la dirección de una ruta de un servidor a un libro de trabajo??
Slds
Hola Ismael,
ResponderEliminarTe comento que tengo una columna de una tabla en excel, que quiero que al ingresar un nombre en unas de esas celdas, se me hipèrvincule automaticamente con el archivo word que está en otro archivo con el mismo nombre que ingresé en la celda.
Desde ya muchas gracias
Hola,
Eliminarquizá te de la idea este post:
http://excelforo.blogspot.com.es/2012/08/vba-un-hipervinculo-con-macros-de-excel.html
Saludos