INDIRECT + VLOOKUP

[Gustavo Azanon-Porres]

I just wondered if you could help....

I would like vlookup to use a lookup_table argument that
has been created using the indirect worksheet function
which points to one excel on our network. The issue we have
is that it appears that when indirect is used in
conjunction with vlookup the underlying file that contains
the source data has got to be open otherwise a Ref! error
is returned = vllokup returns no data unless the source
file is open.

What puzzles me is that vlookup will retrieve the relevant
data if the lookup_table argument has been manually entered
through the wizard!!!

Do you know of a workaround for this limitation?

Thanks

Gustavo

 

[Arvi Laanemets]

Hi

Create a mirror of source table. P.e. when the source table is in workbook
SourceFile on sheet SourceSheet:
1. Open SourceFile
2. In destination workbook, create a sheet p.e. SourceMirror
3. On SourceMirror, enter into A1 the formula:
=IF([SourceFile.xls]SourceSheet!A1="";"";[SourceFile.xls]SourceSheet!A1)
4. Copy the formula at least over same range as source table in
SourceFile.SourceSheet
5. Close SourceFile (and you can now hide the sheet SourceMirror)
6. In your formulas you refer to table on sheet SourceMirror

Arvi Laanemets