Indirect and Path & File Names

Hi

I have the following formula that works a treat if all my 80 Workbooks are
open

SUMPRODUCT((INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to
2005_03_31.xls]Sheet1'!$E$5:$E$10000")'Sales'!C9)*(INDIRECT("'[Sales for
"&$A9&" period 2005_01_01 to
2005_03_31.xls]Sheet1'!$a$5:$a$10000")<'Sales'!$H$6))

Is there a way to arrange the above formula to include the path of the
relevent files so they do not need to be open?

The path is C:\Workbooks\<filename>

TIA

Indirect does NOT work with closed workbooks. Try using named ranges.

--
Don Guillett
SalesAid Software
[email protected]
"Anthony Slater" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I have the following formula that works a treat if all my 80 Workbooks are
> open
>
> SUMPRODUCT((INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to
> 2005_03_31.xls]Sheet1'!$E$5:$E$10000")'Sales'!C9)*(INDIRECT("'[Sales for
> "&$A9&" period 2005_01_01 to
> 2005_03_31.xls]Sheet1'!$a$5:$a$10000")<'Sales'!$H$6))
>
> Is there a way to arrange the above formula to include the path of the
> relevent files so they do not need to be open?
>
> The path is C:\Workbooks\<filename>
>
> TIA

Hi Anthony
I think I'm right in saying that the INDIRECT function will only work across
workbooks if the workbooks are actually open.
I've used a similar technique using the OFFSET function and I always needed
the books open.

Nick

"Anthony Slater" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I have the following formula that works a treat if all my 80 Workbooks are
> open
>
> SUMPRODUCT((INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to
> 2005_03_31.xls]Sheet1'!$E$5:$E$10000")'Sales'!C9)*(INDIRECT("'[Sales for
> "&$A9&" period 2005_01_01 to
> 2005_03_31.xls]Sheet1'!$a$5:$a$10000")<'Sales'!$H$6))
>
> Is there a way to arrange the above formula to include the path of the
> relevent files so they do not need to be open?
>
> The path is C:\Workbooks\<filename>
>
> TIA

Don

Thanks for that, I've been pulling my hair out over that one..

So now the question: -
How can I fit a named range in this formula? (I'm not a user of Named ranges)

"Don Guillett" wrote:

> Indirect does NOT work with closed workbooks. Try using named ranges.
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "Anthony Slater" <[email protected]> wrote in message
> news:[email protected]...
> > Hi
> >
> > I have the following formula that works a treat if all my 80 Workbooks are
> > open
> >
> > SUMPRODUCT((INDIRECT("'[Sales for "&$A9&" period 2005_01_01 to
> > 2005_03_31.xls]Sheet1'!$E$5:$E$10000")'Sales'!C9)*(INDIRECT("'[Sales for
> > "&$A9&" period 2005_01_01 to
> > 2005_03_31.xls]Sheet1'!$a$5:$a$10000")<'Sales'!$H$6))
> >
> > Is there a way to arrange the above formula to include the path of the
> > relevent files so they do not need to be open?
> >
> > The path is C:\Workbooks\<filename>
> >
> > TIA
>
>
>