Conditional link to multiple workbooks

Hi there,

I have numerous workbooks to analyse data. I would like to get all data of the workbooks together in a single summary workbook, i.e. the summary workbook should link or lookup a value in different workbooks, based on a certain value in, say column A of the summary workbooks. All workbooks have the same format and layout.
I.e
Summary workbook Sheet 1
A1 = "A" -> B1 should return the value of cell B1 Sheet 1 of Workbook "FileA"
A2 = "B" -> B2 should return the value of cell B2, Sheet 1 of Workbook "FileB"
etc.

I have tried Match, Index, and Vlookup functions, but can' get it to work.

Any help greatly appreciated.

I have also created a column with the relevant file name through a Vlookup function. So the question is - how can I replace the absolute file path with a relative file path?
Assumed I have the file name and path in Column D
=[FileA.xls]Sheet1!B1
with something like
=[(D1).xls]Sheet1!B1?