All helpful information, but it looks like I am back to square one as it
pertains to using INDIRECT... I still need help figuring out what's wrong
with my syntax.

Again, I am trying to shorten the string(s) because my understanding is that
I need to specify the entire path, even though the sheets are open (files may
be in different directories).

I'm guessing I have a ' or " out of place or something simple, but can't
nail it down!

Also, if there is a more simplified way to express this, such as
concatonating the entire path/filename and then using indirect, let me know!

I greatlly appreciate the assistance.

Ray

"Peo Sjoblom" wrote:

The workbook needs to be open so A1 is mote

There are ways of bypassing this, the 2 that I know are as follows

PULL() by Harlan Grove

ftp://members.aol.com/hrlngrv/ (look for pull.zip)

and Laurent Longre's MOREFUNC

can be downloaded here

http://www.download.com/Morefunc/300...-10423160.html

INDIRECT.EXT is the name of the function



--
Regards,

Peo Sjoblom



"RayportingMonkey" wrote in
message ...
I just can't seem to get the syntax to work... Especially when I try to put
the path in a cell (A1). Plus I need to make the formula even more dynamic
by
defining 5 variables as indicated below:

CELL A1 Path = \\NetworkServer\Mainfolder\DataFolder\Working CELL A1
Folder\
CELL A2 Var1 = FILENAME_
CELL A3 Var2 = XXX
CELL A4 Sheet = SheetName
CELL A5 Range = NamedRange

Such as:
=INDIRECT("'&A1&"&A2&A3&"]A4'!A5")

And ultimately to include in a vlookup or something like this:
=VLOOKUP(A8,(INDIRECT("'PATH["&VAR1&VAR2&"]SHEET'!RANGE")),2,FALSE)
YES - The worksheets are open - Although I am intrigued with INDIRECT.EXT,
I
can't find a working link and also saw comments indicating that it did NOT
support closed files - just like the native INDIRECT.

On another sheet, I am able to use the following formula:
=VLOOKUP(A8,(INDIRECT("'C:\Documents and Settings\ray.legrand\My
Documents\PROJECTS\FSA Report\FSA
Rev.2\["&"TEMPLATE_"&A1&"]Sheet1'!TABLE_ONE")),2,FALSE) but again, when I
attempt to shorten the string by putting the path in a cell reference, it
bombs...

HELP?!