Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Sam Sam is offline
external usenet poster
 
Posts: 699
Default vlookup using different workbooks

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]

  #2   Report Post  
Posted to microsoft.public.excel.misc
Sam Sam is offline
external usenet poster
 
Posts: 699
Default vlookup using different workbooks

or maybe INDIRECT

"sam" wrote:

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]

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default vlookup using different workbooks

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

sam wrote:

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
  #4   Report Post  
Posted to microsoft.public.excel.misc
Sam Sam is offline
external usenet poster
 
Posts: 699
Default vlookup using different workbooks

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.

"Dave Peterson" wrote:

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

sam wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default vlookup using different workbooks

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

sam wrote:

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.

"Dave Peterson" wrote:

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

sam wrote:

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


--

Dave Peterson
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
VLOOKUP 2 workbooks Holly Excel Discussion (Misc queries) 2 February 23rd 09 03:44 PM
vlookup multiple workbooks Churley Excel Discussion (Misc queries) 6 July 31st 06 02:11 PM
Vlookup between two workbooks JodyGlenn Excel Worksheet Functions 0 May 17th 06 11:06 PM
VLookup across Workbooks? PH NEWS Excel Worksheet Functions 0 April 24th 06 03:43 PM
Vlookup between two workbooks kalz Excel Worksheet Functions 1 December 7th 04 11:35 AM


All times are GMT +1. The time now is 06:38 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"