Drop down list as part of a file name selector

[Guest]

I created a drop down list. The list contains the names for certain Excel
files without the extension ".xls" (File1, File1, File3, etc). Those files
are stored in the same directory.
In a different field I want to select data from one of the drop down list
files. In that box I put: " ='B3.xls'!data "
B3 is the location of the drop down list to select the specific file; data
is the cell name in the data file.
Needless to say that this doesn't work, but I hope someone knows how I can
do this differently without using the huge IF-statement that I am using now.
Thanks!

 

[Guest]

You may use the INDIRECT.EXT function, that can fetch references from closed
files. This is not a standard function, you have it available to download
here:
http://xcell05.free.fr/english/
And the help for the function here:
http://xcell05.free.fr/english/morefunc/indirect.ext.htm
In order to craft the reference part, use something like:
"'" & B3 & ".xls'!data "
Concatenating instead of writing the B3 as part of the string.

Hope this helps,
Miguel.