Fórmula genérica
=INDIRECT("'("&workbook&")"&sheet&"'!"&ref)
Resumen
Para crear una referencia de hoja de trabajo dinámica, una referencia a otro libro de trabajo que se crea con una fórmula basada en variables que pueden cambiar, puede usar una fórmula basada en la función INDIRECTA.
En el ejemplo que se muestra, la fórmula en E6 es:
=INDIRECT("'("&B6&")"&C6&"'!"&D6)
Explicación
La esencia de esta fórmula es construir una referencia completa a un rango en otro libro de trabajo como texto, luego usar la función INDIRECTA para convertir el texto en una referencia real.
Una referencia a una hoja de trabajo externa se ve así:
'(datos de muestra.xlsx) Hoja1'! A1
Tenga en cuenta los corchetes (()) alrededor del nombre del libro de trabajo, las comillas simples ('') alrededor de la hoja de trabajo + hoja y el signo de exclamación (!) Que sigue.
Para crear una referencia como esta usando texto, usamos la concatenación para unir valores de las columnas B, C y D con los corchetes, comillas y signos de exclamación requeridos:
=INDIRECT("'("&B6&")"&C6&"'!"&D6)
El resultado se introduce en INDIRECTO como ref_text:
=INDIRECT("'(sample data.xlsx)Sheet1'!A1")
La función INDIRECTO luego evalúa el texto y lo convierte en una referencia. Excel sigue la referencia y devuelve el valor en la referencia dada.
Nota: si la referencia no es válida o si el libro de trabajo al que se hace referencia no está abierto, INDIRECTO arrojará un error #REF. Puede detectar este error con la función IFERROR y mostrar un resultado personalizado si lo desea.
