External reference with variable filenames

[donavank]

I have an excel workbook that needs to externally reference another excel workbook to pull in data. The catch is that the file being reference has a variable file name. The file version will increment with each update (testfilev1.0, testfilev1.1, etc)

I want to be able to use a formula or a macro to get the data from the excel workbook with the variable file name. I also want to be able to see the information in the workbook that contains the external reference without having to open up both workbooks, so I'd prefer not to use the INDIRECT function unless there is a work around so that you don't need to have both files open.

I am using version 2007 of Excel.

Thanks in advance.