Alternative to Indirect()

**erwina** · 09-04-2010, 06:40 PM

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.

**daddylonglegs** · 09-04-2010, 07:49 PM

One option is to install Morefunc add-in (try googling it) and use INDIRECT.EXT function. That's designed to work with closed workbooks.

There aren't any inbuilt formula alternatives to INDIRECT for your problem.

**MarvinP** · 09-04-2010, 07:54 PM

Hi erwina,

Read a thread from a few days ago, similar to your question.

Read it by clicking this link.

**erwina** · 09-04-2010, 08:06 PM

Thanks for the input Marvin.

I followed the thread and arrived here:
http://spreadsheetpage.com/index.php...a_closed_file/

The getvalue function though doesn't seem to be powerful enough for what I need it to do. Thanks for the suggestion, seems I will just need to use MoreFunc if ever, but likely I'll just scale down the automation of the spreadsheet.

my other problem is that we can't install stuff on our workstations here at work, but thanks for the suggestion Daddylonglegs. I will probably have to open a ticket requesting Morefunc to be installed standard to our workstations sometime.