Nombres de archivo en fórmulas

Hola buena día:
Quiero ver si me puedes ayudar, mira te explico; Estoy hacinedo un pequeño sistema en Excel para una escuela, la finalidad es concentrar los resultados de cada parcial de cada una de las materias que imparte cada profesor en un solo archivo para de ahí sacar un reporte final.
Tengo un archivo al que llamo concentrado y dentro de la misma carpeta tengo aproximadamente otros 150 archivos que son los de cada una de las materias que reporta cada profesor (cada archivo está nombrado por materia) los cuales están vinculados por fórmula con varias celdas al archivo concentrado (de los 150 archivos son las mismas hojas y celdas que se toman en cuenta para el concentrado (promedio, num de faltas, num de alumnos etc. Por materia).
En el concentrado, en una columna tengo la lista de todos los nombres de los archivos y lo que quiero es, hacer una fórmula que haga referencia a cada una de las celdas que contiene cada nombre del archivo y que la fórmula me muestre el dato ligado correspondiente a cada materia con el simple hecho de copiarla hacia abajo y ya no sea necesario estar ligando individualmente cada archivo y también cada que cambia el semestre cambian las materias y así con solo poner el nuevo nombre de la materia ya se ligue al nuevo archivo (ya creado con anterioridad) de la nueva materia.
Ya lo intente con la función INDIRECTO y si me da lo que yo quiero el problema es que solo me da el resultado con los archivos abiertos, y estando cerrados los archivos, me marca #¡REF!
Espero haberme explicado.
De antemano doy las gracias por leer mi problema