Problem with Indirect Function

I'm trying to use the indirect function to link to a cell in an open workbook. The Workbook I'm trying to link to is called Table.xls. Here is what I have in the main workbook ...

cell A2 = Table.xls
cell A3 = Sheet1
cell A4 = C5

cell A7 = =INDIRECT(" ' [" & A2 & " ] " & A3 & " ' ! " & A4)

this should access cell C5 on Sheet1 of Table.xls file but it returns #REF error in cell A7 for some reason. I have both workbooks open so I know that isn't the reason. Is there a setting or something I need to change to get this to work? Any help is appreciated. Thanks.

Originally Posted by romperstomper

You appear to have unwanted spaces:
=INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)

Sorry, I just added the spaces to clear up the whole " vs. ' thing. This is what I actually have in A7...=INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)

Originally Posted by NBVC

Also the source workbook must be open for it to work!

Both workbooks are open and I'm still getting the #REF error.

Originally Posted by NBVC

Also the source workbook must be open for it to work!

Both workbooks are open and I'm still getting the #REF error.

Originally Posted by romperstomper

Are they open in the same instance of Excel? (they need to be)

Yes they are!

I deleted the ".xls" from the file name in A2 and it worked. Does anybody know why?

Based on this website (http://www.contextures.com/xlFunctions05.html) the .xls should be in the cell.

table.xls in A2 returns #REF
table in A2 works
table.xlsx in A2 works

Thanks for your help Romperstomper and NBVC

Now to add a twist to the problem, what if I had multiple excel files called Table_1, Table_2, Table_3, etc.. and I wanted to use the indirect function to link to one of these files. So I would have...

cell A2 = Table
cell A3 = 1
cell A4 = Sheet1
cell A5 = C5

which would access the file Table_1.xlsx

How would I write that?