Using a Filename in a cell as part of a formula

I have a very simple problem it would appear but as a novice Excel user I just don't seem to be able to crack it.

I have a folder containing hundreds of identical spreadsheets, each with several worksheets.

I would like to collate them into a summary spreadsheet.

To do this I thought to have a column containing the filenames of the spreadsheets and alongside this a cell referencing the cell in the corresponding spreadsheet. However, I wanted the formula in this cell to use the filename in the first cell as part of that formula.

I just can't seem to get that formula right.

Any help would be appreciated.

Thanks.

Use the =INDIRECT() function.

Thanks yudlugar but it doesn't seem to work. To explain further:

Say I have an open spreadsheet called File1 and I have a value in Cell A1

In spreadsheet File2 Cell A1 contains the filename File1

In Cell A2 I want the value of File1 A1 but the formula in this Cell A2 uses the Filename from Cell A1

Something like ='[DIRECT(A1)]Summary '!A2

Many thanks

Ok, so in File 2 A2:

=Indirect("'[" & A1 & "]Summary'!A2)

I hope it's not me!!!

I put in =INDIRECT('[" & A4 & "]Summary'!D24) and got a #REF! in C4 the cell I put the formula in.

A4 is the actual Cell with the file name and D24 the actual cell in the other spreadsheet. Both spreadsheets are in the same folder. I took out the first " as it was coming up as an error.