I have about fifty files that are located in the same folder. I need to pull a number from the same cell in each file into a separate file. I'd like to do something like this:


Cell Name Formula
A1 filename1 ='FilePath\[A1]Sheet1'!$B$1
A2 filename2
A3 filename3

I'd like to be able to create the formula down to pull B1 for each file. Is there a way to do this in Excel?