Excel 2003 links are not updating between workbooks

[Guest]

A link between two workbooks using the SUMIF function is not updating unless
you open the master workbook. Why isn't the s/s automatically updating?

 

[Dave Peterson]

There are some functions that don't work with closed workbooks. You found one
with =sumif().

But you could use a different formula to replace the =sumif() portion:

=sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95=$f280),
'yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$m$1:$m$95)

(saved from a previous post)

 

[Guest]

Thanks for the information about the closed workbooks. The sumproduct
doesn't work because the criteria used in SUMIF points to selected cells and
this returns the Sum

Example:
Name Number
Neal 1
Neal 2
Patrick 5
Patrick 5
Patrick 5
Tami 10
Tami 20
Tami 30

=SUMIF('[databook.xls]Bogus Numbers'!$A$3:$B$10,A6,'[databook.xls]Bogus
Numbers'!$B$3:$B$10) Result = 60

There are some functions that don't work with closed workbooks. You found one
with =sumif().

But you could use a different formula to replace the =sumif() portion:

=sumproduct(--('yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$G$1:$G$95=$f280),
'yourpath\[GV LP Capital 2005.XLS]SUMMARY'!$m$1:$m$95)

(saved from a previous post)

A link between two workbooks using the SUMIF function is not updating unless
you open the master workbook. Why isn't the s/s automatically updating?

 

[Max]

... The sumproduct doesn't work because the criteria
used in SUMIF points to selected cells and ...

=SUMIF('[databook.xls]Bogus Numbers'!$A$3:$B$10,A6,'[databook.xls]Bogus
Numbers'!$B$3:$B$10) Result = 60

A little late this response, but in case
the thread is still being tracked ...

Think there's a typo in your SUMIF above,
in the part: $A$3:$B$10
which should have read: $A$3:$A$10

Anyway, here's a lightly tested (working)
SUMPRODUCT translation
of the above (following what Dave P suggested earlier)

In say, B6:

=SUMPRODUCT(--('[databook.xls]Bogus Numbers'!$A$3:$A$10=A6),
'[databook.xls]Bogus Numbers'!$B$3:$B$10)

The formula can be copied down the col to return
correspondingly for other values in A7, A8, etc