variable substitution in a formula linking to external workbook

[Guest]

Hi,

I'm trying to link to an external workbook. Let's say the formula is:

G:\2007\2007 Jun\blah.xls

I would like to be able to change "Jun" by referencing a cell. I've read
that the Indirect function cannot be used to reference an external workbook
without it being open. I can't open my external workbook b/c it has the same
file name as the one I'm working on (but they are located in difference
folders, thus, needing to change "Jun" each month). Is there a way to do
this?

Thanks in advance for any help.

 

[Dave Peterson]

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

 

[jamison.folland]

I downloaded the formulas, but I am getting an error. I can't see the
mistake in using the INDIRECT.EXT formula. Can anyone with experience
tell me by looking why this would be incorrect?

=INDIRECT.EXT("'W:\Transportation\Overseas Operatations\FCA\FCA
Reports and Updates\FCA In Transit Reports\["&TEXT(B2,"yyyy.mm.dd")&"
FCA in transit report SZH.xls]Po List'!$A$3")

It works when the reference sheet is open, but returns the #VALUE!
error when I close that sheet...