vlookup worksheet function for external excel files

  • Thread starter Thread starter Gustavo
  • Start date Start date
G

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
 
I don't think =vlookup() has this requirement. Are you using =indirect() with
=vlookup()?
 
Hi

Dave Peterson said:
I don't think =vlookup() has this requirement. Are you using =indirect() with
=vlookup()?
Click to expand...


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
 
Back
Top