Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel Re-calculation 2000 compared to 2003 | Excel Discussion (Misc queries) | |||
Automate Excel to powerpoint - Graphs along with Datasheet (not workbook) | Charts and Charting in Excel | |||
How do I remove external links from Excel 2000 workbook? | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) |