Fórmula genérica
=INDIRECT(sheet&"!"&CELL("address",A1))
Resumen
Para incrementar una referencia creada como texto dentro de la función INDIRECTA, puede usar la función CELDA. En el ejemplo que se muestra, la fórmula en D5 es:
=INDIRECT($B$5&"!"&CELL("address",A1))
Qué se incrementa a medida que se copia la fórmula.
Explicación
Considere una referencia dinámica simple a Sheet2 usando el INDIRECTO en una fórmula como esta:
=INDIRECT($B$5&"!"&"A1"))
Si cambiamos el nombre de la hoja en B5 a otro nombre (válido), INDIRECTO devolverá una referencia a A1 en la nueva hoja.
Sin embargo, si copiamos esta fórmula en la columna, la referencia a A1 no cambiará, porque "A1" está codificado como texto.
Para resolver este problema, usamos la función CELDA para generar una referencia de texto a partir de una referencia de celda regular:
CELL("address",A1)
Con "dirección" como primer argumento y A1 como segundo argumento, la función CELDA devuelve una cadena como "$ A $ 1". Debido a que A1 es una referencia de celda regular, se incrementará normalmente a medida que la fórmula se copia en la columna. El resultado en D5: D9 es una serie de fórmulas como esta:
=INDIRECT("Sheet2!$A$1") =INDIRECT("Sheet2!$A$2") =INDIRECT("Sheet2!$A$3") =INDIRECT("Sheet2!$A$4") =INDIRECT("Sheet2!$A$5")
En cada caso, INDIRECTO resuelve cada cadena de texto en una referencia y Excel devuelve el valor en la celda dada en Sheet2.
Nota: tanto INDIRECTO como CELDA son funciones volátiles y se recalculan con cada cambio de hoja de trabajo. Esto puede causar problemas de rendimiento en hojas de trabajo más complejas.
