Links only update when external workbook is open

G

Guest

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!
 
H

Harlan Grove

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.
Click to expand...
....

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

Harlan Grove

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.
Click to expand...
....

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

Harlan Grove

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.
Click to expand...
....

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

Harlan Grove

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.
Click to expand...
....

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

RagDyer

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 said:
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.
Click to expand...
...

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))
Click to expand...
 
G

Guest

Thanks! That did the trick. Also, I was trying to sum F278:F306. I just
had some fat fingers when I typed the OFFSET formula.

Harlan Grove said:
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.
Click to expand...
....

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))
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DSUM only works if reference workbook is open 1
ISERROR and INDEX Funcion question 8
Referencing Defined Name in another workbook 2
OFFSET: Returns error when used to a linked file 1
Excel Sumproduct Ref error when table is closed 0
how can i update links in destination workbook when source workbo. 1
Baffling problem with links 13
Links to 2nd closed worksheet fail when using offset function ?? 1

Top