How to referencing cells from another workbook using the information (text) inside the destination workbook

[kevin5059]

Hi to whom it may be concern,

http://i60.tinypic.com/123s8xw.jpg

Assume that I have 4 separated excel workbook files named as the following and store in directory C:\

EE3190.xlsx
EE3395.xlsx
EE3467.xlsx
EE3497.xlsx

All data are stored in "Sheet1" in the workbook files mentioned above respectively.

I would like to create a summary workbook to view all my data from various workbooks. I tried to create a Function-linksf (in the diagram) to refer the cells from other workbook. However, I did not get what i wanted. Please help.

The diagram on the right hand side shows what I get from my function after i type the formula (=linksf(A3,"3","B") in the respective cell.

='C:\[EE3190.xlsx]Sheet1'!B3 (the excel doesn't threat '=' as the formula initiator)

However, that is not something I expected. I'm looking for some output like the diagram highlighted in RED box. I know that I can link all those cells one by one using

='C:\[EE3190.xlsx]Sheet1'B3 or etc.

however I want something that can make use of the information in my Student Number column to detect the workbooks and copy the cells for me automatically, so once I change my student number column, my worksheet will refer to the filename accordingly.

Please help, thank you very much. Sorry for my english.

The link for the diagram...
http://i60.tinypic.com/123s8xw.jpg

 

[kevin5059]

Any comment or inputs will be helpful. thank.