A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

vlookup using different workbooks



 
 
Thread Tools Display Modes
  #1  
Old October 1st 09, 02:18 AM posted to microsoft.public.excel.misc
Sam
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]

Ads
  #2  
Old October 1st 09, 02:28 AM posted to microsoft.public.excel.misc
Sam
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  
Old October 1st 09, 01:55 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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  
Old October 2nd 09, 01:46 AM posted to microsoft.public.excel.misc
Sam
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  
Old October 2nd 09, 02:15 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 03:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.