vlookup using different workbooks

[sam]

hi everyone

i am using the vlookup function to get data from another worksheet in
another workbook. this all works fine but my issue is i am constantly
changing the title of the workbookn (different versions) with the data in it.
is there a way to use a wildcard in a file name in the VLOOKUP function?

eg.
VLOOKUP($E3,'[Workbook v2.xls]worksheet'!$A:B$,2,false]

i want to turn it into:
VLOOKUP($E3,'[Workbook v3.xls]worksheet'!$A:B$,2,false]

 

[sam]

or maybe INDIRECT

 

[Dave Peterson]

What you'd want to use is =indirect() and build a formula that results in the
string that points at the folder, file, sheet, location.

But the bad thing is that =indirect() won't work if that sending file is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.

hi everyone

i am using the vlookup function to get data from another worksheet in
another workbook. this all works fine but my issue is i am constantly
changing the title of the workbookn (different versions) with the data in it.
is there a way to use a wildcard in a file name in the VLOOKUP function?

eg.
VLOOKUP($E3,'[Workbook v2.xls]worksheet'!$A:B$,2,false]

i want to turn it into:
VLOOKUP($E3,'[Workbook v3.xls]worksheet'!$A:B$,2,false]

 

[sam]

Thanks for the reply Dave

I am thinking of using INDIRECT inside the VLOOKUP. I am using 2003 and
understand that the file needs to be open to use this function. What is I am
trying to use is:

=VLOOKUP($E3,INDIRECT("'[Workbook v"&"'"&".xls]worksheet'!$A:B$"),2,false]

The "'" is meant to sub the number (2 for 3) of the version. What am I doing
wrong? Is this possible to do it this way?

Thanks,

Sam.

What you'd want to use is =indirect() and build a formula that results in the
string that points at the folder, file, sheet, location.

But the bad thing is that =indirect() won't work if that sending file is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.

hi everyone

i am using the vlookup function to get data from another worksheet in
another workbook. this all works fine but my issue is i am constantly
changing the title of the workbookn (different versions) with the data in it.
is there a way to use a wildcard in a file name in the VLOOKUP function?

eg.
VLOOKUP($E3,'[Workbook v2.xls]worksheet'!$A:B$,2,false]

i want to turn it into:
VLOOKUP($E3,'[Workbook v3.xls]worksheet'!$A:B$,2,false]

 

[Dave Peterson]

I'm not sure what you're doing, but if you put the number (2 or 3) in A1 of the
same sheet, you could use something like:

=VLOOKUP($E3,INDIRECT("'[Workbook v"&a1&".xls]worksheet'!A:B"),2,false)

Watch your typing, too.
A:B or $a:$b, but not $a:b$.
And close with a ), not a ].

Thanks for the reply Dave

I am thinking of using INDIRECT inside the VLOOKUP. I am using 2003 and
understand that the file needs to be open to use this function. What is I am
trying to use is:

=VLOOKUP($E3,INDIRECT("'[Workbook v"&"'"&".xls]worksheet'!$A:B$"),2,false]

The "'" is meant to sub the number (2 for 3) of the version. What am I doing
wrong? Is this possible to do it this way?

Thanks,

Sam.

What you'd want to use is =indirect() and build a formula that results in the
string that points at the folder, file, sheet, location.

But the bad thing is that =indirect() won't work if that sending file is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.

hi everyone

i am using the vlookup function to get data from another worksheet in
another workbook. this all works fine but my issue is i am constantly
changing the title of the workbookn (different versions) with the data in it.
is there a way to use a wildcard in a file name in the VLOOKUP function?

eg.
VLOOKUP($E3,'[Workbook v2.xls]worksheet'!$A:B$,2,false]

i want to turn it into:
VLOOKUP($E3,'[Workbook v3.xls]worksheet'!$A:B$,2,false]