vlookup across different workbooks

  • Thread starter Thread starter cgeier
  • Start date Start date
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
 
What you need is, build shell code to open the books you need on open
event from the book where you got the vlookup, this by VBA
 
Hi
see your other post

--
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=277492
Click to expand...
 
Back
Top