Hi everyone, I have a spreadsheet that looks up data from other files. As usual I have an Indirect() problem, wherein the file has to be open for it to work. The reason why I use Indirect is because my I want my lookup formula to reference another cell for the filename of the other spreadsheets. I can't figure out a workaround using Index or the other usual workarounds suggested.

Here is what my formula looks like:

=VLOOKUP($C$5,INDIRECT(CONCATENATE("'\\3.206.0.237\files\[",$B15,"]compsheet'!$B$3:$AK$27")),E$4,false)

Where C5 contains a name I use for looking up.
B15 is where the name of the spreadsheet is -- this changes so I want to it to be replaceable.

Originally the formula was a lot simpler and looked like the usual:

=VLOOKUP(C5,'\\3.206.0.237\files\[2010-07.xls]compsheet'!$B$3:$P$22,15,FALSE)

But this does't have the replaceable filename.