Links only update when external workbook is open

CMB wrote...
....
>1. Using the OFFSET function:
>
>=SUM(OFFSET('[Daily-Data.xls]Daily'!$F$5,273,0,303,1))
....
> 2. Using the INDEX function:
>
>=SUM(INDEX('[Daily-Data.xls]Daily'!$F$5:$F$369,274):
>INDEX('[Daily-Data.xls]Daily'!$F$5:$F$369,302))
....
>Both of these formulas work fine when the source workbook is open.
....

These two formulas sum different ranges. The OFFSET formula sums
F278:F580 while the INDEX formula sums F278:F306. Which do you want?

The short but technical answer is that you're using both OFFSET and
INDEX to return range references, but external references into closed
workbooks *NEVER* resolve to range references, rather they're returned
as arrays.

You need to use something like the following.

=SUMPRODUCT('[Daily-Data.xls]Daily'!$F$1:$F$580,
(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)>=k)
*(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)<=n))

If you want to sum F278:F580, make it

=SUMPRODUCT('[Daily-Data.xls]Daily'!$F$1:$F$580,
(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)>=278)
*(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)<=580))

In case you can't see your posts, I see 4 of them.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1131053547.781036.248240@o13g2000cwo.googlegroups.com...
> CMB wrote...
> ...
> >1. Using the OFFSET function:
> >
> >=SUM(OFFSET('[Daily-Data.xls]Daily'!$F$5,273,0,303,1))
> ...
> > 2. Using the INDEX function:
> >
> >=SUM(INDEX('[Daily-Data.xls]Daily'!$F$5:$F$369,274):
> >INDEX('[Daily-Data.xls]Daily'!$F$5:$F$369,302))
> ...
> >Both of these formulas work fine when the source workbook is open.
> ...
>
> These two formulas sum different ranges. The OFFSET formula sums
> F278:F580 while the INDEX formula sums F278:F306. Which do you want?
>
> The short but technical answer is that you're using both OFFSET and
> INDEX to return range references, but external references into closed
> workbooks *NEVER* resolve to range references, rather they're returned
> as arrays.
>
> You need to use something like the following.
>
> =SUMPRODUCT('[Daily-Data.xls]Daily'!$F$1:$F$580,
> (ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)>=k)
> *(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)<=n))
>
> If you want to sum F278:F580, make it
>
> =SUMPRODUCT('[Daily-Data.xls]Daily'!$F$1:$F$580,
> (ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)>=278)
> *(ROW('[Daily-Data.xls]Daily'!$F$1:$F$580)<=580))
>