Reference another workbook in a formula

[dlmeyers]

I am having trouble inserting a cell reference into a formula. I have a spreadseet that uses data from another workbook. The workbook name changes monthly, so i'd like to be able to place the workbook name in a cell and have the formulas in the spreadsheet use that cell to get the name of the workbook to get the data from. So:

if, for eg, the formula in my spreadsheet is ='[test data file.xlsx]Sheet1'!$B$4+'[test data file.xlsx]Sheet1'!$B$6

I would like to be able to enter test dta file.xlsx into cell A2 of my spreadsheet and replace a reference to A2 into the formulas.
I've tried several ways, but can't seem to get it to work.
Any suggestions?

[NBVC]

=Indirect("'["&A2&".xlsx]Sheet1'!$B$4")+Indirect("'["&A2&".xlsx]Sheet1'!$B$6")

but workbook must be open...

if closed, need to install addin and use Morefunc.xll

=Indirect.ext("'["&A2&".xlsx]Sheet1'!$B$4")+Indirect.ext("'["&A2&".xlsx]Sheet1'!$B$6")

[dlmeyers]

Thank you. I had tried Indirect, but i guess didn't have the appropriate combination of "'s and brackets!!!!

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save