Formula with cell reference not working

I can get the following to work jsut fine:
='C:\test\TAL\[TAL_3.xlsx]TAL'!$D$11

However I need to have "TAL_3.xlsx" by a dynamic reference to a cell.

I have tried the following with no luck:
=INDIRECT("'C:\test\TAL\[" & INDIRECT("A8") & "TAL_1.xlsx]TAL'!$D$11")

Big picture I have a column of data that contains, TAL_1, TAL_2, TAL_3, etc. For each TAL_# I need to pull a status from that file. Trying to avoid a macro...but that might end up being the solution.

Is there anyway to get this to work. Other languages have an 'eval' command that would interpret a variable, but have not found anything like this in excel.

Would appreciate any help.

Thanks in advance!

Ron

Well that works...partially. After a little more debugging it looks like the file has to be open . Is there a way to have this coded so I can just open the one file, and the foruma will automatically look in the other files without them having to be open???

Thanks again for the help.

Hi

INDIRECT requires the file to be open before it will work.

I believe that morefunc has some addins that will work with a closed file, but I've never used them.

rylo