VLOOKUP on external file

[Charlie]

This works:

=VLOOKUP("TEST",'C:\Folder\[File.xls]Sheet1'!A:A,1,FALSE)

This does not:
(In cell C3) =CONCATENATE("'C:\Folder\[File.xls]Sheet1'!A:A")

=VLOOKUP("TEST",C3,1,FALSE)

=VLOOKUP("TEST",INDIRECT(C3),1,FALSE)

Is there a formula, function, or syntax I need to use to reference an
external file if the file is described in a cell?

TIA
Charlie

 

[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.

This works:

=VLOOKUP("TEST",'C:\Folder\[File.xls]Sheet1'!A:A,1,FALSE)

This does not:
(In cell C3) =CONCATENATE("'C:\Folder\[File.xls]Sheet1'!A:A")

=VLOOKUP("TEST",C3,1,FALSE)

=VLOOKUP("TEST",INDIRECT(C3),1,FALSE)

Is there a formula, function, or syntax I need to use to reference an
external file if the file is described in a cell?

TIA
Charlie

 

[Charlie]

Thanks. I'll take a look tomorrow. Heading home.

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.

This works:

=VLOOKUP("TEST",'C:\Folder\[File.xls]Sheet1'!A:A,1,FALSE)

This does not:
(In cell C3) =CONCATENATE("'C:\Folder\[File.xls]Sheet1'!A:A")

=VLOOKUP("TEST",C3,1,FALSE)

=VLOOKUP("TEST",INDIRECT(C3),1,FALSE)

Is there a formula, function, or syntax I need to use to reference an
external file if the file is described in a cell?

TIA
Charlie