Excel 2003 links are not updating between workbooks
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? |
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) Tami C wrote: 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 |
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 "Dave Peterson" wrote: 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) Tami C wrote: 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 |
"Tami C" wrote:
... 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
All times are GMT +1. The time now is 05:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com