Problem With Reference Update

[Egon]

I'm having a problem with Excel not updating references in a countif
formula to another workbook. Other spreadsheets are working fine, but
this one I have isn't updating properly.

Automatic calculation is turned on and even doing a force with F9
doesn't make any difference. The only way I can make it pull in the
values from the other workbook is if I open those other work books.
This isn't going to be a problem for another week, but being that they
are doing a calculation based on a workbook that is created daily its
going to be a problem having 30 of them opened at the end of the month.

Any suggestions on why this isn't work would be greatly appreciated.

J.

 

[Dave Peterson]

Some functions won't work with closed workbooks. =countif() is one of them.

But you can replace each of your =countif()'s with =sumproduct().

=SUMPRODUCT(--('c:\[aaaa.xls]bbbb'!B2:B41="whatever you want here"))

could be a replacement.

 

[Egon]

Thanks a ton. I'll update it first thing in the AM and see if it works.

So SUMPRODUCT will work exactly as COUNTIF does?

 

[Egon]

Well, I tried your suggestion of using SUMPRODUCT and it doesn't do the
same thing as count if, unless I'm not understanding how you have the
formula written...

what's the "(--(" piece?

J.

 

[Dave Peterson]

=sumproduct() likes to work with numbers. the -- changes trues and falses to
+1's and 0's.

I'd guess that there was something wrong with your formula.

You may want to post your =countif() (with the other workbook open) and your
=sumproduct() version, too.

 

[Egon]

here is my countif statement

=COUNTIF('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul 05.xls]DMR'!$K$128:$N$137,
"Fugro")

The Sumproduct I used was

=SUMPRODUCT('S:\Major Projects\6917 Gomez\revenue and cost
tracking\vessel repoerts\[PS-MR-RP-DMR 01 JUL
05.xls]DMR'!$K$128:$N$137="Fugro")

I don't think the Sumproducts is right, but I'm not sure that I'm sure
how it should be.

Thanks for you help.
J

 

[Egon]

Ok, I have the following as my SUMPRODUCTS function:

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$254="Fugro"))

And I'm runing 3 of these on different parts of a workbook. 3 Different
parts of the same workbook. But the problem is the 3rd time its doing
it, it refused to look at the unopened workbook.

Any suggestions?

J.

 

[Dave Peterson]

My bet is you have a spelling error in the third formula.

If you open the other workbook (nice for testing and nice for posting--since it
shortens the formulas), you may see the problem quicker.

If you don't see the problem, open the other workbook first, then copy each of
the 3 formulas to your followup post.

I like to build these formulas when I have the other workbook open. Then I can
start my formula:

=sumproduct(--(
And use the mouse to point at the other range.

Then I don't have to worry about any syntax rules at all. When I close the
workbook, excel will do the heavy lifting and fix all my formulas.

Ok, I have the following as my SUMPRODUCTS function:

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$254="Fugro"))

And I'm runing 3 of these on different parts of a workbook. 3 Different
parts of the same workbook. But the problem is the 3rd time its doing
it, it refused to look at the unopened workbook.

Any suggestions?

J.

 

[Egon]

Here are the three Formulas as shown with the file Open:

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$128:$N$177="Canyon"))

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul 05.xls]DMR'!$K$12:$N$75="Canyon"))

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$300="canyon"))

In this form, with the file open, all three work.

Here are the same three formulas when the file is closed.

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$128:$N$177="Canyon"))

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$12:$N$75="Canyon"))

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$300="canyon"))

When the file is closed, the last of these won't update. but the first
two update perfectly.

Think maybe Sumproduct is limited to the number of instances or
something? But that doesn't make sense because there are 4 sets of
these formulas and the first 2 ALWAYS work and the last always fails if
the file is closed.

J.

 

[Dave Peterson]

I don't see anything wrong with your formulas. There is a limit of 1024
characters in the formula (when measured in R1C1 notation). But you're not
close to that.

So you open the other workbook and you get one set of answers.

Then close the other workbook and that third formula evaluates to something else
(when you recalc)?

Just a wild guess...

Do you have calculation set for automatic (tools|options|calculation tab)

I don't have a real guess,

sorry.

Here are the three Formulas as shown with the file Open:

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$128:$N$177="Canyon"))

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul 05.xls]DMR'!$K$12:$N$75="Canyon"))

=SUMPRODUCT(--('[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$300="canyon"))

In this form, with the file open, all three work.

Here are the same three formulas when the file is closed.

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$128:$N$177="Canyon"))

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$K$12:$N$75="Canyon"))

=SUMPRODUCT(--('S:\Major Projects\6917-11 Gomez\revenue and cost
tracking\Vessel Reports\[PS-MR-RP-DMR 01 Jul
05.xls]DMR'!$N$182:$Q$300="canyon"))

When the file is closed, the last of these won't update. but the first
two update perfectly.

Think maybe Sumproduct is limited to the number of instances or
something? But that doesn't make sense because there are 4 sets of
these formulas and the first 2 ALWAYS work and the last always fails if
the file is closed.

J.

 

[Egon]

I have calculations set to Automatic, what I get when the workbook
isn't open is #N/A on the last formula.

 

[Dave Peterson]

Do you have any #n/a's in that range that you're trying to count?

(But then you'd get the same #n/a if the workbook is open or closed.)

 

[Egon]

No, there aren't any #N/A's in the range that I'm look at, its all
company names. So I don't understand. Oddly enough, ONE of the
SUMPRODUCT functions now works this morning but the others are still
broken. I've built a macro that opens and closes all the files that are
references, but maybe its time to learn VBA and build a small app that
does the calculations for me.

Still not sure what excel is up to, thanks for your help Richard, if
you have any ideas I'm sure up for hearing them.

J.

 

[Dave Peterson]

Richard?


Do you have any UDF's in your workbook?

Under some versions of excel (xl97, IIRC), if the UDF encountered an unhandled
error, it could affect calculation.

 

[Egon]

I don't have any UDF's that should be unhandled that I'm aware of, but
its certainly odd behavior for sure. I'm thinking that maybe using VBA
to build it might be better and more stable anyway at this point.

J.

 

[Dave Peterson]

One more try???

Open excel in safe mode
(Close excel first)
windows start button|run
excel /safe
file|open yourworkbook.xls

Does it recalculate correctly?

(Did you try this already?)

 

[Roberto Serrano]

I am having troubles updating SUMIF formulas when the link workbooks are
closed. It does work when I open them.

Any idea how to resolve

 

[Dave Peterson]

Saved from a previous post, but a more specific example:

The bad news is =sumif() won't work when you close your other workbook.

You could use =sumproduct() instead, though.

=SUMPRODUCT(--('[Dynamics Open Order Report.XLS]Dyn stock'!$A$4:$A$64998=$P2),
'[Dynamics Open Order Report.XLS]Dyn stock'!$E$4:$E64998)

Build the formula with that workbook open. Then you can verify that it works.
When you close the workbook, excel will add the path and folder.

In fact, I'd let excel do the work...

type
=sumproduct(--(

and point to A4:A64998 of the dyn stock worksheet.

(Do you really need all those rows? It could take a while to recalc.)