There is, yes. I did exactly this the other day. You have to have all the files open.
Use this:
=INDIRECT(“‘C:\Expenses\file.xlsx]Expenses’!AL37”)
Where AL37 is the cell I want and Expenses is the sheet name.
File.xlsx has to be open though.
I had a load of files that were named as dates like this 2010-10-10.xlsx so I made a column with the dates in it and then used this to generate the file name where C was the column with the dates in it
=INDIRECT(“‘C:\Expenses\[” & TEXT(YEAR(C2), “00”) & “-” & TEXT(MONTH(C2), “00”) & “-” & TEXT(DAY(C2), “00”) & “.xlsx]Expenses’!AL37”)
You can download a function from the ‘net that do the same as INDIRECT but will open the file if it’s not open. It’s called PULL, this link may work but you may wish to google further.