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.