I have a formula that sums a range from a column in another workbook. The
formula works fine when the source workbook is open. However, when the source
workbook is closed, I get a #VALUE! error or a !REF# error depending on the
function I use.

I've tried to compute the sum using both the OFFSET and INDEX functions, but
to no avail.

Here are two things I've tried:

1. Using the OFFSET function:

=SUM(OFFSET('[Daily-Data.xls]Daily'!$F$5,273,0,303,1))

When the Daily-Data.xls workbook is closed I get a #VALUE! error. I heard
that OFFSET is a volatile function. So I rewrote my formula to use the
non-volatile INDEX function.


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))

However, when the Daily-Data.xls workbook is closed I get a #REF! error.

Both of these formulas work fine when the source workbook is open.

What's the best way to sum a range of columns from another worksheet without
having to open the source worksheet each time??

Thanks!