Excel 2003 won't read Excel 97

[telewats]

I have created a spreadsheet that has numerous links, formulas, etc.. I
send this spreadsheet to several people each day. We've found that
those with Excel 2003 cannot read one of my columns. That column
contains a COUNTIF formula linking to another spreadsheet.

When the 03 users attempt to open the file, they can see the contents
of the cells I'm referring to above. However, when they answer the
question about linking other files (whether they answer yes or no), the
cells change to the #VALUE! error....

Has anyone ever had this happen and/or do you know how to fix this?

 

[Alan]

As far as I know, COUNTIF will only work when referring to another workbook
if that workbook is open.
I use 2003 too, I dont know if it works in earlier versions. It can be done
in other ways like SUMPRODUCT etc,
Regards,
Alan.

 

[Mark Lincoln]

Are macros being blocked in those users' Excel options?

 

[Dave Peterson]

xl2002+ likes to recalculate any workbooks that were created in previous
versions. In earlier versions of excel, if you answer No to the update links
prompt, the existing values are kept. In xl2002+, you get those errors.

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

maybe it'll help you.

 

[Guest]

CountIF isn't native to Excel, it's part of the add-in workbook Conditional
Sum Wizard I believe. You can have your users check TOOLS/ADD INS and see if
it is checked off, but I don't remember if that add-in was included in the 97
version.

 

[Mike Middleton]

Kevin -

The COUNTIF worksheet function is built into Excel 97 (and subsequent
versions). It is not part of an add-in.

- Mike
www.mikemiddleton.com

 

[telewats]

I'm thinking maybe the problem is a combination of what everyone wrote
I have other formulas/macros all through my spreadsheet, and the value
for these can be read by the Excel 2003 users; only the column that ha
=COUNTIF formulas cannot. So, assuming it's a problem with thi
particular formula, can someone tell me how to rewrite my formula s
that it will give me the same info? Here's an example of the formul
I'm currently using:

=COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 1234567*")
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 1234589*")
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 4561234*")
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 6751678*")

There is info on either side of >5 Days : xxxxxxx so I have to have th
asterik. Can this be done with a SUMPRODUCT or another formula?

Or, do you think it's a different problem altogether that I need t
address

 

[Dave Peterson]

There are some worksheet functions that don't work with closed workbooks.

=indirect(), =sumif(), =countif() are a few.

But maybe you could use a different formula:

=sumproduct(--(isnumber(search(">5 days",
'O:\[FILEB.xls]REPORT 1'!$A$1:$a$999)))

(all one cell)

Extend the range, but you can't use the whole column.

ps. I only did the first portion of your sum.

I'm thinking maybe the problem is a combination of what everyone wrote.
I have other formulas/macros all through my spreadsheet, and the values
for these can be read by the Excel 2003 users; only the column that has
=COUNTIF formulas cannot. So, assuming it's a problem with this
particular formula, can someone tell me how to rewrite my formula so
that it will give me the same info? Here's an example of the formula
I'm currently using:

=COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 1234567*") +
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 1234589*") +
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 4561234*") +
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 6751678*")

There is info on either side of >5 Days : xxxxxxx so I have to have the
asterik. Can this be done with a SUMPRODUCT or another formula?

Or, do you think it's a different problem altogether that I need to
address?

 

[telewats]

Thanks for the tip. Two more questions: does this formula work with the
asterisks? and how do I combine more than one criteria with this
formula? In other words, after
=sumproduct(--(isnumber(search(">5 days",'O:\[FILEB.xls]REPORT
1'!$A$1:$a$999))) do I include the next portion by starting with
,(--(isnumber... or is this done some other way? I've tried a couple
things already with no success.

 

[Dave Peterson]

Since you're searching for the text, you won't need the asterisks (if I
understand correctly)...

And one way would be...

=COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 1234567*") +
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 1234589*") +
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 4561234*") +
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,"*>5 Days : 6751678*")

Becomes:

=sumproduct(--(isnumber(search(">5 days : 1234567",
'O:\[FILEB.xls]REPORT 1'!$A$1:$a$999)))
+
sumproduct(--(isnumber(search(">5 days : 1234589",
'O:\[FILEB.xls]REPORT 1'!$A$1:$a$999)))
+
(and keep going.)

Thanks for the tip. Two more questions: does this formula work with the
asterisks? and how do I combine more than one criteria with this
formula? In other words, after
=sumproduct(--(isnumber(search(">5 days",'O:\[FILEB.xls]REPORT
1'!$A$1:$a$999))) do I include the next portion by starting with
,(--(isnumber... or is this done some other way? I've tried a couple
things already with no success.

 

[telewats]

I'm getting this formula to work with one set of criteria, but it won'
count corrctly when I add more than one, and it will only allow me t
enter a max of six criteria in the formula anyway. I've checked an
double-checked to be sure I'm entering it just like Dave's example, bu
it still won't work. Is there some kind of limit with SUMPRODUCT o
ISNUMBER or some other part of this formula that only allows for on
factor

 

[Dave Peterson]

You could post the formula you're using--or while you're testing, you could
break each piece into a different cell. Just to help you find the mistake.

 

[telewats]

I have finally gotten this formula to work... it appears it had to do
with the parentheses and how they were placed. Each time I'd write the
formula, I'd apparently miss some, and Excel would auto-fix it for me.
It was placing all the paretheses at the end of the formula instead of
after each set of criteria, so I wasn't getting the correct results.
Anyway, I've finally gotten it to work out, and I just wanted to say
thanks, because I wouldn't have gotten it to work at all if it hadn't
been for everyone posting their helpful hints. Thanks!

 

[Dave Peterson]

Sometimes it's good to not let excel fix the formula--I think we've all been
there.