Linking between spreadsheets does not work unless I have both spr.

[Guest]

I am using Excel 2003 and I have upgraded from office 97. Before the upgrade
the spreadsheets that we were using here at work updated without a problem.
Since the upgrade the main spreadsheet we use will not properly update unless
all the spreadsheets that is gets information from are open. I go to the
links and update and it give me the OK status. I set excel to maunally
update. But no matter what I try everytime I update the main spreadsheet
without all the others open it will not properly update the data. The odd
part is that I cane created a new spreadsheet and I can get information off
of those same spreadsheets untill I try to use a SUMIF or COUNTIF statement.

 

[Guest]

From what I've experienced with the newer versions of Excel is that SUMIF
won't pull information from a file that isn't open. I'm not sure on COUNTIF.
For what I was doing I had to put the SUMIF's in the sheet I wanted to pull
the information from and then use VLOOKUP so all the files didn't have to be
open.

 

[Dave Peterson]

My bet is that the formulas didn't work in xl97, either (with the other
workbooks closed).

But xl2002+ handle the links differently when it opens that workbook.

In earlier versions, you could say "no" to the update links prompt and excel
would continue to show the results of the previous calculation.

xl2002+ shows the #ref! errors.

You have a couple of choices.

#1. Change the behavior of xl2002+ so that it behaves the way earlier versions
of excel did.

#2. Change your formula to something that works with closed workbooks.

(My choice would be #2.)

But for #1...Jim Rech posted a registry tweak:
http://groups.google.com/[email protected]

If you choose #2, you can use the array formula =sum(if()) or even sumproduct.

Saved from a previous post (maybe you'll see how you can modify your existing
formula):

{=SUM(IF('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green",
'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10))}

=sumproduct(--('C:\New Project\[school 5.XLS]Sheet1'!$D$1:$D$10="green"),
'C:\New Project\[school 5.XLS]Sheet1'!$E$1:$E$10)

by the way, the =sum(if(...
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

===
both of these formulas cannot use the whole column.