Summary Sheet of Multiple Files

I wonder if you know can help solve an excel problem I have.

I have hundreds spreadsheets all in the same directory on a server, all with similar names (test1.xls, test2.xls etc.)

Each spreadsheet is identical in format, only the data changes and I want to summarize certain cells on a separate spreadsheet. For simplicity I would want to see the value from test1 cell A1, B1, C1, D1 and E1.

Currently I copy the line above and paste it below and then do a search and replace for the next filename I want to add. Is there a way that I can type the name of the spreadsheet in the the summary sheet and then the formula would incorporate the name from that cell and automatically retrieve the data from the other sheets?

Does that make sense? Can it be done without VBA (i'm not a VBA user yet)

To do it without VBA, you could put the first part of the formula in a cell, say J1

'C:\Your Path...\[Test

then in cell B2 put

="='"&$J$1&ROW(B2)-1&".xls]Sheet1'!"&RIGHT(B$1,2)

and copy this to all the required cells (note the ' and " after the second equal sign). Then copy the whole range and Paste Special - Values. This will give the correct formulas in all the cells but they won't return the values until you make them calculate by Edit - Replace -Find what := Replace with:= Replace All