Vlookup From Closed Workbook with variable filename

Hi There,

I have a number of Workbooks that contain research i have conducted on various stocks.

All of these Workbooks have a title page that contains summary information that i deem important, with all the different workbooks having the exact same title page.

I would like to build a master document that allows me to enter the 3 digit stock code and then get the front page from this stock. I think it is best to use an example:

In my master document i would like to have:

A1: Stock Code
A2: File Destination (this would be static as all the different files are within the same folder)
A3: The file name (This would need to be dynamic based on the value in A1) eg "CDD History.xlsx"

A4 and below to display the front page information from the "CDD History.xlsx" Workbook

The reason i would like this to be dynamic is so i can easily enter in various stock codes and get the different summary information quickly and easily.

I am happy to upload both the "CDD History" and how i would like the master document to work if that helps?

I have so far tried to use the "=Pull" Formula from Harlan Grove and have also tried using the INDIRECT formula but none of these are working.

ANy help would be greatly appreciated.

Kind Regards,
Will

Sorry i am actually using MS excel 2010. Will this still work?

It is 32bit Excel 2010.

I have downloaded and installed this file however i'm not really sure how it works?

Are there any other suggestions using VBA or something?