Un dato de todas las hojas, escrito una sola vez

Esta semana surgió en la comunidad un reto muy habitual cuando un libro tiene muchas hojas: mostrar el valor de la celda B3 de cada hoja, indicando los nombres de las hojas en una lista y escribiendo la fórmula una sola vez.

El punto de partida era la función INDIRECTO, que construye una referencia a partir de texto:

``
=INDIRECTO("'"&D3&"'!B3")
`

El inconveniente es que esta fórmula hay que arrastrarla hacia abajo celda a celda: si en D3, D4, D5... tienes los nombres de las hojas, necesitas una copia por cada fila.

La solución que se propuso evita el arrastre combinando BYROW y LAMBDA sobre el rango desbordado de nombres de hoja (D3#):

`
=BYROW(D3#;LAMBDA(x;INDIRECTO("'"&x&"'!B3")))
`

BYROW recorre cada nombre de hoja del rango D3# y, para cada uno, INDIRECTO arma la referencia 'NombreHoja'!B3 y devuelve su valor. Una sola fórmula, escrita una vez, que se expande automáticamente a tantas filas como hojas tengas listadas.

Como enfoque alternativo, se apuntó también a las referencias 3D de Excel, útiles cuando el resultado va en una única celda y las hojas están consecutivas.

Un apunte sobre INDIRECTO`: es una función volátil (se recalcula con frecuencia) y trabaja con texto, así que no se actualiza sola si renombras una hoja. Para libros muy grandes conviene tenerlo en cuenta, pero para consolidar datos por nombre de hoja es de lo más práctico.

El problema: el mismo dato en muchas hojas

Cuando un libro de Excel tiene decenas de hojas con la misma estructura, tarde o temprano necesitas lo mismo: recoger el valor de una celda concreta —pongamos B3— de cada una de esas hojas y reunirlo en una lista, sin escribir la fórmula cincuenta veces. Es uno de esos retos que aparecen una y otra vez en la comunidad de Influexcel, y tiene una solución sorprendentemente limpia.

El punto de partida: INDIRECTO

La primera herramienta que surge es INDIRECTO, la función que convierte texto en una referencia real. Si en la celda D3 tienes escrito el nombre de una hoja, esta fórmula te trae su celda B3:

`` =INDIRECTO("'"&D3&"'!B3") ``

Las comillas simples alrededor del nombre son importantes: permiten que funcione aunque la hoja se llame con espacios o caracteres raros. El inconveniente es que esta fórmula hay que arrastrarla hacia abajo, una copia por cada hoja. Si mañana añades tres hojas más, toca volver a arrastrar.

La solución elegante: BYROW + LAMBDA

Aquí entran las funciones de matriz dinámica. Si tienes los nombres de hoja en un rango que se desborda —por ejemplo D3#—, puedes recorrerlos todos de una sola vez:

`` =BYROW(D3#;LAMBDA(x;INDIRECTO("'"&x&"'!B3"))) ``

BYROW va tomando cada nombre de hoja del rango D3# y se lo entrega a la LAMBDA como la variable x. Dentro, INDIRECTO construye la referencia 'NombreHoja'!B3 y devuelve su contenido. El resultado es una sola fórmula, escrita una única vez, que se expande automáticamente a tantas filas como nombres tengas listados. Añades una hoja a la lista y la respuesta crece sola.

La alternativa: referencias 3D

Cuando el resultado no es una lista sino un único número —por ejemplo la suma de B3 de todas las hojas— y estas están consecutivas en el libro, las referencias 3D de Excel son imbatibles por sencillez:

`` =SUMA(Primera:Ultima!B3) ``

Excel recorre todas las hojas situadas entre Primera y Ultima y opera sobre su B3. No necesitas ni nombres ni texto, pero pierdes flexibilidad: dependes del orden físico de las pestañas.

Un apunte importante sobre INDIRECTO

INDIRECTO es una función volátil: se recalcula muy a menudo, así que en libros enormes puede pesar. Además trabaja con texto, lo que tiene una consecuencia práctica: si renombras una hoja, la fórmula no se actualiza sola, porque el texto del nombre sigue apuntando al nombre viejo. Para consolidar datos por nombre de hoja es de lo más práctico, pero conviene tenerlo presente.

Funciones clave

  • INDIRECTO: convierte una cadena de texto en una referencia de celda válida.
  • BYROW: recorre un rango fila a fila aplicando una LAMBDA a cada una.
  • LAMBDA: define la operación que se repite en cada fila.
  • Referencias 3D (Hoja1:HojaN!Celda): agregan una misma celda a lo largo de varias hojas consecutivas.

Conclusión

El mismo objetivo —un dato de cada hoja, escrito una sola vez— admite dos caminos según lo que necesites: BYROW+INDIRECTO cuando quieres una lista dinámica que crezca sola, y las referencias 3D cuando buscas un único agregado y tus hojas están ordenadas. Este tipo de comparativas, con el problema real y varias soluciones sobre la mesa, es justo lo que se cuece cada semana en la comunidad de Influexcel.

Más contenido de Excel en InflueXcel