VLookup to multiple files

[s m]

Hi,

I am trying to setup a summary sheet which looks to multiple files for
data.

summary.xlsb looks up to the same cells in file1.xlsb, file2.xlsb, and
file3.xlsb.

I would like to have a sheet in summary.xlsb which lists the file
paths so that users can just add a path and then a row in the summary
sheet using that path to add the lookups to a new file (i.e
file4.xlsb)

I have been able to accomplish this using the VLOOKUP formula and the
path, but when I try to use the path in the sheet cell I get the #REF!
error

This works:
=VLOOKUP(B5,'C:\Documents and Settings\username\My Documents\Client
\Status\[vlookup_test.xlsb]Sheet1'!$A$1:$B$3,2, FALSE)

This doesn't work:
=VLOOKUP(B5,Sheet2!A4,2) where Sheet2!A4 = 'C:\Documents and Settings
\username\My Documents\Client\Status\[vlookup_test.xlsb]Sheet1'!$A$1:$B
$3

I'm happy to use a macro if this is a better solution.

Thankls in advance,

--Simon

 

[Dave Peterson]

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

Hi,

I am trying to setup a summary sheet which looks to multiple files for
data.

summary.xlsb looks up to the same cells in file1.xlsb, file2.xlsb, and
file3.xlsb.

I would like to have a sheet in summary.xlsb which lists the file
paths so that users can just add a path and then a row in the summary
sheet using that path to add the lookups to a new file (i.e
file4.xlsb)

I have been able to accomplish this using the VLOOKUP formula and the
path, but when I try to use the path in the sheet cell I get the #REF!
error

This works:
=VLOOKUP(B5,'C:\Documents and Settings\username\My Documents\Client
\Status\[vlookup_test.xlsb]Sheet1'!$A$1:$B$3,2, FALSE)

This doesn't work:
=VLOOKUP(B5,Sheet2!A4,2) where Sheet2!A4 = 'C:\Documents and Settings
\username\My Documents\Client\Status\[vlookup_test.xlsb]Sheet1'!$A$1:$B
$3

I'm happy to use a macro if this is a better solution.

Thankls in advance,

--Simon