vlookup accross different workbooks

C

cgeier

I have several Excel work books saved in the save format (i.e.
FY03SCHD.XLS, FY04SCHD.XLS, ....)
Each of these have the worksheets set up in the same format (i.e.
January03, Febuary03, ....)

What I would like to do is create a VLOOKUP that would access th
correct work book/sheet based on a date.

I have been able to break the date down and get the correct file nam
already. -NO PROBLEM

What I can not do is get it to link if the work book is not currentl
open, but will if it is open.

Here is the correct path ....
=VLOOKUP($A4,'G:\SHIP\PLANNER\SCHEDULE\[FY03SCHD.XLS]February03'!$A$5:$BB$32,3)

Here is how I set up the link that work when the work book is open ...
=VLOOKUP($A3,INDIRECT("'G:\SHIP\PLANNER\SCHEDULE\[FY"&E3&"SCHD.XLS]"&F3&"'!$A$5:$BB$32"),3)

I have since found out that INDIRECT is a volatile function and that i
why it only work when the source work book is open.

Do you think you could help me out
 
F

Frank Kabel

Hi
you may take a look at:
http://tinyurl.com/2c62u

Though in your case INDIRECT.EXT won't work. You may try Harlan Grove's
pull function mentioned in this thread

--
Regards
Frank Kabel
Frankfurt, Germany

cgeier said:
I have several Excel work books saved in the save format (i.e.
FY03SCHD.XLS, FY04SCHD.XLS, ....)
Each of these have the worksheets set up in the same format (i.e.
January03, Febuary03, ....)

What I would like to do is create a VLOOKUP that would access the
correct work book/sheet based on a date.

I have been able to break the date down and get the correct file name
already. -NO PROBLEM

What I can not do is get it to link if the work book is not currently
open, but will if it is open.

Here is the correct path ....
=VLOOKUP($A4,'G:\SHIP\PLANNER\SCHEDULE\[FY03SCHD.XLS]February03'!$A$5:$
BB$32,3)

Here is how I set up the link that work when the work book is open ....=VLOOKUP($A3,INDIRECT("'G:\SHIP\PLANNER\SCHEDULE\[FY"&E3&"SCHD.XLS]"&F3
&"'!$A$5:$BB$32"),3)

I have since found out that INDIRECT is a volatile function and that is
why it only work when the source work book is open.

Do you think you could help me out?


--
cgeier
--------------------------------------------------------------------- ---
cgeier's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16370
View this thread: http://www.excelforum.com/showthread.php?threadid=277496
Click to expand...
 
B

Bill

In the workbook that will look at the others, define names for the ranges in
the other workbooks.

Insert>Name>Define

Range Name => Feburary03Table
RefersTo => 'G:\SHIP\PLANNER\SCHEDULE\[FY03SCHD.XLS]February03'!$A$5:$BB$32
<Add>

= VLOOKUP ($A$4,Feburary03Table,3)

wj
 
Top