INDIRECT Link of Variables for VLOOKUPs

[RayportingMonkey]

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?!

 

[Peo Sjoblom]

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/3000-2077_4-10423160.html

INDIRECT.EXT is the name of the function

 

[z.entropic]

.... but please be forewarned that INDIRECT.EXT from MOREFUNC slows down the
Excel incredibly, especially if the accessed closed worksheets are on a
network drive. I had to give it up, even though it did the job as
advertised. I wish the next version of Excel could use INDIRECT with closed
worksheets; what's the big problem here?

z.entropic

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/3000-2077_4-10423160.html

INDIRECT.EXT is the name of the function



--
Regards,

Peo Sjoblom

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?!

 

[Harlan Grove]

... but please be forewarned that INDIRECT.EXT from MOREFUNC slows down
the Excel incredibly, . . .

....

Not nearly as much as my pull(..) udf would!

. . . I wish the next version of Excel could use INDIRECT with closed
worksheets; what's the big problem here?

Purely syntactic. INDIRECT only returns range references, and ranges can
only exist (as instantiative Range objects) in open workbooks. Either
Excel's developers would either need to expand the definition of the Range
class to encompass aggregations of cells in closed workbooks as ranges, or
they'd have to change the syntax of INDIRECT so that it could return range
references to ranges in open workbooks or arrays of values of referenced
cells in closed workbooks. The latter is more likely, but twice or even 100
times infinitesimal is still pretty unlikely.

 

[RayportingMonkey]

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

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/3000-2077_4-10423160.html

INDIRECT.EXT is the name of the function



--
Regards,

Peo Sjoblom

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?!

 

[RayportingMonkey]

Actually... Since I am still building the repositories for the data (multiple
Excel Workbooks at roughly 20-50MB each and growing...), would it be better
query these via an OLAP Cube (Excel, Access, Other?)? I've not really messed
with them, so I don't know if that would be better suited -OR- if the lookups
can reference user input, which is what I need to do on this report.

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

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/3000-2077_4-10423160.html

INDIRECT.EXT is the name of the function



--
Regards,

Peo Sjoblom

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?!

 

[Harlan Grove]

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

If the files are open, you DO NOT have to specify drive/directory path.
Excel can't open more than one file at a time with the same base filename,
which is why external references into open files only show filename.

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

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

....

It should be sufficient to use

=VLOOKUP(A8,INDIRECT("'["&"TEMPLATE_"&A1&"]Sheet1'!TABLE_ONE"),2,0)

*IF* the defined name TABLE_ONE were a workSHEET-level name defined in
Sheet1 of this other workbook, but if TABLE_ONE were a workBOOK-level
defined name, you need to drop the square brackets and the worksheet name,
so change the formula to

=VLOOKUP(A8,INDIRECT("'"&"TEMPLATE_"&A1&"'!TABLE_ONE"),2,0)