View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson Dave Peterson is offline
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