Un índice de hojas que se genera solo: HYPERLINK en rangos desbordados

Esta semana surgió en la comunidad un pequeño "expediente X". Un miembro llegó tras ver un vídeo con una idea clara en la cabeza: montar una página de contenidos dinámica donde una sola fórmula desbordara la lista de nombres de hoja del libro y cada celda del resultado enlazara con su hoja correspondiente. Su intento no funcionaba y no entendía por qué.

Había partido de una fórmula con BYROW:

``
=BYROW(B3#;LAMBDA(s;HYPERLINK("#'"&INDICE(s;1)&"'!A1";INDICE(s;1))))
`

El problema es sutil. Como apuntó Nacho, HYPERLINK dentro de un array dinámico toma la celda donde vive la fórmula como dirección para todo el rango desbordado: todos los enlaces del spill acaban apuntando al mismo sitio en vez de cada uno a su hoja.

La solución llegó de la mano de John, que le daba vueltas al asunto desde hacía tiempo hasta que un día "hizo clic":

`
=SUSTITUIR(HYPERLINK("#'"&B3#&"'!A1";);0;B3#)
`

La clave está en dejar que HYPERLINK desborde directamente sobre el rango de nombres de hoja B3# (sin envolverlo en BYROW) y usar SUSTITUIR` para que en cada celda aparezca el texto visible correcto. Con una sola fórmula, la página de contenidos se actualiza sola cada vez que cambian las hojas del libro.

Se incluye el fichero de ejemplo con la página de contenidos dinámica montada sobre las hojas del libro.

El reto: una página de contenidos que se actualiza sola

Un libro con muchas hojas se navega mucho mejor si tiene una página de contenidos: una lista con el nombre de cada hoja donde, al hacer clic, saltas directamente a ella. Lo ideal es que esa lista sea dinámica: que una sola fórmula genere todos los nombres y que cada uno enlace con su hoja, de modo que si añades o quitas pestañas, el índice se actualice sin tocar nada.

Un miembro de la comunidad llegó con esa idea clara tras ver un vídeo, pero su fórmula no funcionaba y no entendía por qué. El diagnóstico es un detalle sutil que merece la pena conocer.

Por qué falla el primer intento

El planteamiento inicial envolvía HIPERVINCULO dentro de un BYROW:

`` =BYROW(B3#;LAMBDA(s;HIPERVINCULO("#'"&INDICE(s;1)&"'!A1";INDICE(s;1)))) ``

Parece razonable, pero hay una trampa. Como explicó Nacho, HIPERVINCULO dentro de un array dinámico toma como destino la celda donde vive la fórmula, la misma para todo el rango desbordado. El resultado: todos los enlaces del spill apuntan al mismo sitio en lugar de cada uno a su hoja. La lista se ve bien, pero los clics están rotos.

La solución: dejar que HIPERVINCULO se desborde

La respuesta la trajo John, que le daba vueltas al problema desde hacía tiempo:

`` =SUSTITUIR(HIPERVINCULO("#'"&B3#&"'!A1";);0;B3#) ``

La clave es doble:

  • No envolver HIPERVINCULO en BYROW. Al pasarle directamente el rango desbordado B3#, la función se desborda ella sola y cada celda del resultado calcula su propio destino 'NombreHoja'!A1.
  • Usar SUSTITUIR para arreglar el texto visible. Al dejar vacío el segundo argumento de HIPERVINCULO, cada enlace muestra un 0; SUSTITUIR reemplaza ese 0 por el nombre de hoja correspondiente de B3#.

Con una sola fórmula, la página de contenidos queda montada y se regenera sola cada vez que cambian las hojas del libro.

Funciones clave

  • HIPERVINCULO: crea un enlace navegable; con #'Hoja'!A1 salta dentro del propio libro.
  • SUSTITUIR: reemplaza un texto por otro; aquí, el marcador por el nombre real de la hoja.
  • B3#: el operador # referencia todo el rango desbordado que generan los nombres de hoja.

Un matiz sobre el marcador

Usar el 0 como marcador funciona, pero tiene un flanco débil que ya ha aparecido en otros retos de la comunidad: si algún nombre de hoja contiene un cero, SUSTITUIR lo reemplazaría también. Para un índice a prueba de balas conviene elegir como marcador un carácter que jamás aparezca en el nombre de una hoja.

Conclusión

Este pequeño "expediente X" deja una lección muy útil sobre cómo se comportan las funciones que generan referencias dentro de arrays dinámicos: HIPERVINCULO mira siempre a la celda que la aloja. Entenderlo permite pasar de una fórmula que "casi funciona" a un índice de hojas autoactualizable en una sola línea. Retos así, con el problema real y la solución afinada entre varios, son el pan de cada día en la comunidad de Influexcel.

Más contenido de Excel en InflueXcel