Using a dynamic pathname in VLOOKUP

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

 

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

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

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

--
Biff
Microsoft Excel MVP

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?

 

[Mike D]

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

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

--
Biff
Microsoft Excel MVP

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?

 

[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

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

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

--
Biff
Microsoft Excel MVP

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?

 

[Harlan Grove]

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

...

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.