Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 87
Default INDIRECT Link of Variables for VLOOKUPs

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?!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,268
Default INDIRECT Link of Variables for VLOOKUPs

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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default INDIRECT Link of Variables for VLOOKUPs

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

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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default INDIRECT Link of Variables for VLOOKUPs

"z.entropic" wrote...
... 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.


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 87
Default INDIRECT Link of Variables for VLOOKUPs

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






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 87
Default INDIRECT Link of Variables for VLOOKUPs

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.

"RayportingMonkey" wrote:

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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,231
Default INDIRECT Link of Variables for VLOOKUPs

"RayportingMonkey" wrote...
....
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!

....
"RayportingMonkey" wrote in

....
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_O NE"),2,0)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions Mike Barlow Excel Worksheet Functions 7 May 21st 23 07:42 PM
VLOOKUPS Brett Excel Worksheet Functions 5 June 22nd 06 02:50 PM
Using Indirect Function to link to Other Files Timmy Mac1 Excel Discussion (Misc queries) 1 July 27th 05 12:38 PM
vlookups Valerie S. Excel Worksheet Functions 0 January 28th 05 01:55 AM
Vlookups wmjenner Excel Worksheet Functions 2 November 23rd 04 11:39 PM


All times are GMT +1. The time now is 10:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"