Using a dynamic pathname in VLOOKUP

M

Mike D

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(pathname,
workbook/sheetname)), 3, FALSE). How do I go about dynamically creating the
full pathname for the file?
 
T

T. Valko

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/Business/MS_Office_Add-ins/Morefunc.html
 
M

Mike D

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 said:
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/Business/MS_Office_Add-ins/Morefunc.html

--
Biff
Microsoft Excel MVP


Mike D said:
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(pathname,
workbook/sheetname)), 3, FALSE). How do I go about dynamically creating
the
full pathname for the file?
 
T

T. Valko

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

--
Biff
Microsoft Excel MVP


Mike D said:
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 said:
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/Business/MS_Office_Add-ins/Morefunc.html

--
Biff
Microsoft Excel MVP


Mike D said:
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(pathname,
workbook/sheetname)), 3, FALSE). How do I go about dynamically
creating
the
full pathname for the file?
 
M

Mike D

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

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

--
Biff
Microsoft Excel MVP


Mike D said:
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 said:
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/Business/MS_Office_Add-ins/Morefunc.html

--
Biff
Microsoft Excel MVP


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(pathname,
workbook/sheetname)), 3, FALSE). How do I go about dynamically
creating
the
full pathname for the file?
 
T

T. Valko

See this:

http://groups.google.com/group/microsoft.public.excel.programming/msg/f95e47088ac12d71

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 said:
Any other suggestions? I still can't get to either of the web sites.

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

--
Biff
Microsoft Excel MVP


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

:

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/Business/MS_Office_Add-ins/Morefunc.html

--
Biff
Microsoft Excel MVP


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(pathname,
workbook/sheetname)), 3, FALSE). How do I go about dynamically
creating
the
full pathname for the file?
 
H

Harlan Grove

T. Valko said:
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/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075
 
T

T. Valko

Harlan Grove said:
...

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/microsoft.public.excel.worksheet.functions/msg/ac443753560f0075

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top