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?

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.
--
HTH,
Barb Reinhardt



"Bentam7" wrote:

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?

From Excel help on the INDIRECT function:
"If ref_text refers to another workbook (an external reference), the other
workbook must be open. If the source workbook is not open, INDIRECT returns
the #REF! error value."
--
David Biddulph

"Bentam7" wrote in message
...
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?

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.

Bentam7 wrote:

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?

--

Dave Peterson