#Ref! error using INDIRECT for file name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bentam7 - 27 Mar 2008 00:12 GMT

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?

Reply to this Message

Barb Reinhardt - 27 Mar 2008 00:16 GMT

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.

Signature

HTH,
Barb Reinhardt

> I'm having trouble with the following formula
>
[quoted text clipped - 5 lines]
> save the values like a link. I tried putting this formula in a SUMPRODUCT()
> but it made no differnce. Any ideas?

Reply to this Message

David Biddulph - 27 Mar 2008 00:17 GMT

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

> I'm having trouble with the following formula
>
[quoted text clipped - 10 lines]
> SUMPRODUCT()
> but it made no differnce. Any ideas?

Reply to this Message

Dave Peterson - 27 Mar 2008 02:03 GMT

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
>
[quoted text clipped - 5 lines]
> save the values like a link. I tried putting this formula in a SUMPRODUCT()
> but it made no differnce. Any ideas?

Signature

Dave Peterson

Reply to this Message