vlookup worksheet function for external excel files

[Gustavo]

I am using vlookup to retrieve data in an external excel
file -i.e. the data I am trying to retrieve is not stored
in the same workbook nor network drive as the records I am
trying to find data for. Unfortunately, vlookup requires
that the data source excel sheet file is open if it is to
return records. Do you know if there is a way to retrieve
data using vlookup while keeping the source closed.

Thanks and regards,

Gustavo

 

[Dave Peterson]

I don't think =vlookup() has this requirement. Are you using =indirect() with
=vlookup()?

 

[Arvi Laanemets]

Hi

I don't think =vlookup() has this requirement. Are you using =indirect() with
=vlookup()?

But anyway it will be a good idea mirroring the source table into current
workbook. Especially when the file path is long, and formulas are complex.



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