vlookup file specification

[Guest]

can i reference a file location in a separate cell for vlookup? i have a
whole column of numbers that come from different files, but they tend to move
around to different folders quite a bit. plus, if it works, i'd like to use
it as a template. however, i don't want to have to change every cell's
lookup. if i could just have the file location and name in a cell somewhere
and change that one spot instead of individual formulas, it'd be great. it
seems like something that should be so easy!

 

[Guest]

do you mean the target table of the vlookup would change folders all the
time??You could have a cell or formulas that recorded the filename or path
and filename of your worksheets but that would have to updated manually via
the edit-> links menu.Even then i am not sure you could put those paths into
a vlookup...if you did that the links in the vlookup formulas could be kept
updated too....

 

[Ragdyer]

Try this, with lookup value in A1 and sheet name in A2:

=VLOOKUP(A1,INDIRECT(A2&"!A1:B15"),2,0)

 

[Andy Wiggins]

Yes, but only if the file, you want to reference, is open.

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.
http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip

It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.


--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy