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

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 casos con estas funciones

• LAMBDA
• BYROW

Más contenido de Excel en InflueXcel

• Funciones window en Excel: el total del grupo en cada fila con LAMBDA y BYROW Tutorial
  En SQL se llaman funciones window: columnas que, para cada fila, traen un agregado calculado sobre un grupo mayor. El total de ese cliente a
• Payback y Payback Descontado en una sola fórmula con BYROW Caso
  Henry necesitaba calcular el Payback y el Payback Descontado de una inversión, y tenía dos fórmulas separadas con LET + SCAN que funcionaban
• Buscar prefijos de longitud variable en otra columna: BYROW, MAP, REGEX y COINCIDIRX Caso
  Interesante problema planteado por un miembro: tiene una columna A con ~1.200 referencias de longitud variable y una columna C con ~276 text
• Descomposición de Cholesky con matrices dinámicas: de VBA a LAMBDA+REDUCE Caso
  Juan Pablo lanza un reto al grupo: tiene una descomposición de Cholesky resuelta con VBA y quiere saber si se puede hacer con matrices dinám
• Filtrar con múltiples condiciones usando matrices booleanas y BYROW Caso
  Interesante problema planteado por un miembro sobre cómo filtrar datos que cumplan varias condiciones simultáneas usando operaciones matrici
• Eliminar filas en blanco de una tabla con FILTRAR y BYROW Caso
  Óscar plantea una duda práctica: tiene una tabla en Excel con filas que contienen información y otras vacías, y necesita extraer solo las fi
• Unir las columnas de cada fila con BYROW sin que sobre el separador del final: doce maneras Caso
  Juan tenía dos columnas por fila, un código (A.5) y una descripción (b) Prestaciones de servicios), y quería unirlas con un guion en medio:
• ¿MAP o BYROW? Descubre cuándo usar cada una Tutorial
  ¿MAP o BYROW? Descubre cuándo usar cada una y cómo estas funciones pueden transformar tu forma de trabajar en Excel. En este vídeo aprenderá
• Eliminar valores de una matriz dinámica con tabla de exclusiones y REDUCE Caso
  Juan tiene una matriz generada con fórmulas de desbordamiento y necesita eliminar ciertos textos (nombres de áreas geográficas como "Área me
• Separar artículos concatenados en filas: 4 enfoques (fórmulas, Power Query y Python) Caso
  Caso interesante con cuatro enfoques muy distintos para resolver un mismo problema: una tabla tiene una columna de artículos concatenados co