VLOOKUP on external file
 

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

VLOOKUP on external file
 

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.

Charlie wrote:

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

VLOOKUP on external file
 

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

"Dave Peterson" wrote:

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.

Charlie wrote:

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