Dynamic Link

How can I create a dynamic link to another excel file? I want the user to type in a name in cell A1 and in cell B1 it is linked to the file name typed in A1 and reads a cell value in that file. Can this be done through a function such as =[name.xls]Sheet1!C20 where "name" is what was typed in cell A1?

Originally Posted by CPGUY33

How can I create a dynamic link to another excel file? I want the user to type in a name in cell A1 and in cell B1 it is linked to the file name typed in A1 and reads a cell value in that file. Can this be done through a function such as =[name.xls]Sheet1!C20 where "name" is what was typed in cell A1?

=INDIRECT("["&A1&".xls]Sheet1!B3")

works if the file is open. (untested otherwise)
---

=INDIRECT("["&A1&".xls]Sheet1!B3")
works if the file is open. (untested otherwise)

Does anyone know of a way to do this with the source spreadsheet closed?

Thanks!