Using a dynamic pathname in VLOOKUP
 

I'm trying to access different files on the network using VLOOKUP. The
pathname for the files will be created using the CONCATENATE command. I can
generate a valid address using the ADDRESS command when I'm trying to
reference a particular cell, but VLOOKUP requires a range. If you used the
full pathname in VLOOKUP it would look like this VLOOKUP(lookup_value,
'directory_path\[workbook.xls]sheet1'!B2:B100, 3, FALSE) and this works fine.
In my case the pathname will remain constant, but the workbook and sheet
will change. I tried the following two commands and neither works VLOOKUP
(lookup_value, ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):B100,3, FALSE) and VLOOKUP(lookup_value,
ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):ADDRESS(100,2,1,TRUE,Concatenate(pathn ame,
workbook/sheetname)), 3, FALSE). How do I go about dynamically creating the
full pathname for the file?

Using a dynamic pathname in VLOOKUP
 

You could use the INDIRECT function to "build" the reference *BUT* this
would require the source file to be open in order to work. This is usually
not desireable. An alternative is a free addin that might work for you (I've
don't have this particular addin but have seen it mentioned here quite
often).

Here's a link to the site but for some reason I'm currently unable to gain
acess to it (keep getting a 403 error)

Look for the Morefunc addin. You need the INDIRECT.EXT function.

http://xcell05.free.fr/english/

Here's an alternative download site:

http://www.freedownloadscenter.com/B.../Morefunc.html

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I'm trying to access different files on the network using VLOOKUP. The
pathname for the files will be created using the CONCATENATE command. I
can
generate a valid address using the ADDRESS command when I'm trying to
reference a particular cell, but VLOOKUP requires a range. If you used
the
full pathname in VLOOKUP it would look like this VLOOKUP(lookup_value,
'directory_path\[workbook.xls]sheet1'!B2:B100, 3, FALSE) and this works
fine.
In my case the pathname will remain constant, but the workbook and sheet
will change. I tried the following two commands and neither works VLOOKUP
(lookup_value, ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):B100,3, FALSE) and VLOOKUP(lookup_value,
ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):ADDRESS(100,2,1,TRUE,Concatenate(pathn ame,
workbook/sheetname)), 3, FALSE). How do I go about dynamically creating
the
full pathname for the file?

Using a dynamic pathname in VLOOKUP
 

I was not able to download from either link you provided. Both links say I
don't have access to download from those locations.

"T. Valko" wrote:

You could use the INDIRECT function to "build" the reference *BUT* this
would require the source file to be open in order to work. This is usually
not desireable. An alternative is a free addin that might work for you (I've
don't have this particular addin but have seen it mentioned here quite
often).

Here's a link to the site but for some reason I'm currently unable to gain
acess to it (keep getting a 403 error)

Look for the Morefunc addin. You need the INDIRECT.EXT function.

http://xcell05.free.fr/english/

Here's an alternative download site:

http://www.freedownloadscenter.com/B.../Morefunc.html

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I'm trying to access different files on the network using VLOOKUP. The
pathname for the files will be created using the CONCATENATE command. I
can
generate a valid address using the ADDRESS command when I'm trying to
reference a particular cell, but VLOOKUP requires a range. If you used
the
full pathname in VLOOKUP it would look like this VLOOKUP(lookup_value,
'directory_path\[workbook.xls]sheet1'!B2:B100, 3, FALSE) and this works
fine.
In my case the pathname will remain constant, but the workbook and sheet
will change. I tried the following two commands and neither works VLOOKUP
(lookup_value, ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):B100,3, FALSE) and VLOOKUP(lookup_value,
ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):ADDRESS(100,2,1,TRUE,Concatenate(pathn ame,
workbook/sheetname)), 3, FALSE). How do I go about dynamically creating
the
full pathname for the file?

Using a dynamic pathname in VLOOKUP
 

The authors site must be experiencing some sort of glitch. Others have noted
the same. Just keep trying.

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I was not able to download from either link you provided. Both links say I
don't have access to download from those locations.

"T. Valko" wrote:

You could use the INDIRECT function to "build" the reference *BUT* this
would require the source file to be open in order to work. This is
usually
not desireable. An alternative is a free addin that might work for you
(I've
don't have this particular addin but have seen it mentioned here quite
often).

Here's a link to the site but for some reason I'm currently unable to
gain
acess to it (keep getting a 403 error)

Look for the Morefunc addin. You need the INDIRECT.EXT function.

http://xcell05.free.fr/english/

Here's an alternative download site:

http://www.freedownloadscenter.com/B.../Morefunc.html

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I'm trying to access different files on the network using VLOOKUP. The
pathname for the files will be created using the CONCATENATE command.
I
can
generate a valid address using the ADDRESS command when I'm trying to
reference a particular cell, but VLOOKUP requires a range. If you used
the
full pathname in VLOOKUP it would look like this VLOOKUP(lookup_value,
'directory_path\[workbook.xls]sheet1'!B2:B100, 3, FALSE) and this works
fine.
In my case the pathname will remain constant, but the workbook and
sheet
will change. I tried the following two commands and neither works
VLOOKUP
(lookup_value, ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):B100,3, FALSE) and VLOOKUP(lookup_value,
ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):ADDRESS(100,2,1,TRUE,Concatenate(pathn ame,
workbook/sheetname)), 3, FALSE). How do I go about dynamically
creating
the
full pathname for the file?

Using a dynamic pathname in VLOOKUP
 

Any other suggestions? I still can't get to either of the web sites.

"T. Valko" wrote:

The authors site must be experiencing some sort of glitch. Others have noted
the same. Just keep trying.

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I was not able to download from either link you provided. Both links say I
don't have access to download from those locations.

"T. Valko" wrote:

You could use the INDIRECT function to "build" the reference *BUT* this
would require the source file to be open in order to work. This is
usually
not desireable. An alternative is a free addin that might work for you
(I've
don't have this particular addin but have seen it mentioned here quite
often).

Here's a link to the site but for some reason I'm currently unable to
gain
acess to it (keep getting a 403 error)

Look for the Morefunc addin. You need the INDIRECT.EXT function.

http://xcell05.free.fr/english/

Here's an alternative download site:

http://www.freedownloadscenter.com/B.../Morefunc.html

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I'm trying to access different files on the network using VLOOKUP. The
pathname for the files will be created using the CONCATENATE command.
I
can
generate a valid address using the ADDRESS command when I'm trying to
reference a particular cell, but VLOOKUP requires a range. If you used
the
full pathname in VLOOKUP it would look like this VLOOKUP(lookup_value,
'directory_path\[workbook.xls]sheet1'!B2:B100, 3, FALSE) and this works
fine.
In my case the pathname will remain constant, but the workbook and
sheet
will change. I tried the following two commands and neither works
VLOOKUP
(lookup_value, ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):B100,3, FALSE) and VLOOKUP(lookup_value,
ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):ADDRESS(100,2,1,TRUE,Concatenate(pathn ame,
workbook/sheetname)), 3, FALSE). How do I go about dynamically
creating
the
full pathname for the file?

Using a dynamic pathname in VLOOKUP
 

See this:

http://groups.google.com/group/micro...5e47088ac12d71

Your only other option is Harlan Grove's "pull" UDF.

Notice in that post Dave also recommends the site I mentioned.

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
Any other suggestions? I still can't get to either of the web sites.

"T. Valko" wrote:

The authors site must be experiencing some sort of glitch. Others have
noted
the same. Just keep trying.

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I was not able to download from either link you provided. Both links
say I
don't have access to download from those locations.

"T. Valko" wrote:

You could use the INDIRECT function to "build" the reference *BUT*
this
would require the source file to be open in order to work. This is
usually
not desireable. An alternative is a free addin that might work for you
(I've
don't have this particular addin but have seen it mentioned here quite
often).

Here's a link to the site but for some reason I'm currently unable to
gain
acess to it (keep getting a 403 error)

Look for the Morefunc addin. You need the INDIRECT.EXT function.

http://xcell05.free.fr/english/

Here's an alternative download site:

http://www.freedownloadscenter.com/B.../Morefunc.html

--
Biff
Microsoft Excel MVP


"Mike D" wrote in message
...
I'm trying to access different files on the network using VLOOKUP.
The
pathname for the files will be created using the CONCATENATE
command.
I
can
generate a valid address using the ADDRESS command when I'm trying
to
reference a particular cell, but VLOOKUP requires a range. If you
used
the
full pathname in VLOOKUP it would look like this
VLOOKUP(lookup_value,
'directory_path\[workbook.xls]sheet1'!B2:B100, 3, FALSE) and this
works
fine.
In my case the pathname will remain constant, but the workbook and
sheet
will change. I tried the following two commands and neither works
VLOOKUP
(lookup_value, ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):B100,3, FALSE) and VLOOKUP(lookup_value,
ADDRESS(2,2,1,TRUE,Concatenate(pathname,
workbook/sheetname)):ADDRESS(100,2,1,TRUE,Concatenate(pathn ame,
workbook/sheetname)), 3, FALSE). How do I go about dynamically
creating
the
full pathname for the file?

Using a dynamic pathname in VLOOKUP
 

"T. Valko" wrote...
....
Your only other option is Harlan Grove's "pull" UDF.

Notice in that post Dave also recommends the site I mentioned.
....

Problem is the xcell.free.fr site seems to be fubar or history. Good news is
that there are several web sites that carry the current version - 4.2 - of
MOREFUNC.XLL. Google search should turn up several. Probably not authorized,
but with Longre's site unusable, better than nothing unless he means not to
distribute it anymore nor let anyone else do so.

But there are other alternatives.

http://groups.google.com/group/micro...443753560f0075

Using a dynamic pathname in VLOOKUP
 

"Harlan Grove" wrote in message
...
"T. Valko" wrote...
...
Your only other option is Harlan Grove's "pull" UDF.

Notice in that post Dave also recommends the site I mentioned.
...

Problem is the xcell.free.fr site seems to be fubar or history. Good news
is that there are several web sites that carry the current version - 4.2 -
of MOREFUNC.XLL. Google search should turn up several. Probably not
authorized, but with Longre's site unusable, better than nothing unless he
means not to distribute it anymore nor let anyone else do so.

But there are other alternatives.

http://groups.google.com/group/micro...443753560f0075


Ah, yes. I've seen that post, you've probably linked to it before. I think
I'll keep that link for future reference.

I hadn't even thought of option 1. Kind of kludgy if you need something
that's dynamic but it's better than nothing.

--
Biff
Microsoft Excel MVP