variable substitution in a formula linking to external workbook

M

my

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.
 
J

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top