#Ref! error using INDIRECT for file name

[Bentam7]

I'm having trouble with the following formula

=INDIRECT("'S:\14 Finance\SALES\07-08\[Week "&$B41&" 07-08.xls]Summary'!d118")

where $b41 refers to a cell with a week number in it. When ever I open this
file it returns a #Ref! error unless I open this particular file references
in the formula. I need to do this everytime the file is opened, iot doesn't
save the values like a link. I tried putting this formula in a SUMPRODUCT()
but it made no differnce. Any ideas?

 

[Barb Reinhardt]

Some functions don't work unless the workbook containing the info needed is
opened. I"m not sure if this is one of the. Open it and check.

 

[Dave Peterson]

The good news is that Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that allows you to return values from closed
workbooks.

I'm having trouble with the following formula

=INDIRECT("'S:\14 Finance\SALES\07-08\[Week "&$B41&" 07-08.xls]Summary'!d118")

where $b41 refers to a cell with a week number in it. When ever I open this
file it returns a #Ref! error unless I open this particular file references
in the formula. I need to do this everytime the file is opened, iot doesn't
save the values like a link. I tried putting this formula in a SUMPRODUCT()
but it made no differnce. Any ideas?