software

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?--
telewats
------------------------------------------------------------------------
telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
View this thread: www.excelforum.com/showthread...hreadid=508308As 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.
quot;telewatsquot; gt; wrote in
message ...
gt;
gt; I have created a spreadsheet that has numerous links, formulas, etc.. I
gt; send this spreadsheet to several people each day. We've found that
gt; those with Excel 2003 cannot read one of my columns. That column
gt; contains a COUNTIF formula linking to another spreadsheet.
gt;
gt; When the 03 users attempt to open the file, they can see the contents
gt; of the cells I'm referring to above. However, when they answer the
gt; question about linking other files (whether they answer yes or no), the
gt; cells change to the #VALUE! error....
gt;
gt; Has anyone ever had this happen and/or do you know how to fix this?
gt;
gt;
gt; --
gt; telewats
gt; ------------------------------------------------------------------------
gt; telewats's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30270
gt; View this thread: www.excelforum.com/showthread...hreadid=508308
gt;
Are macros being blocked in those users' Excel options?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:
groups.google.com/groups?thre...GP11.phx .gbl

maybe it'll help you.

telewats wrote:
gt;
gt; I have created a spreadsheet that has numerous links, formulas, etc.. I
gt; send this spreadsheet to several people each day. We've found that
gt; those with Excel 2003 cannot read one of my columns. That column
gt; contains a COUNTIF formula linking to another spreadsheet.
gt;
gt; When the 03 users attempt to open the file, they can see the contents
gt; of the cells I'm referring to above. However, when they answer the
gt; question about linking other files (whether they answer yes or no), the
gt; cells change to the #VALUE! error....
gt;
gt; Has anyone ever had this happen and/or do you know how to fix this?
gt;
gt; --
gt; telewats
gt; ------------------------------------------------------------------------
gt; telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
gt; View this thread: www.excelforum.com/showthread...hreadid=508308

--

Dave Peterson

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.
--
Kevin Backmannquot;telewatsquot; wrote:

gt;
gt; I have created a spreadsheet that has numerous links, formulas, etc.. I
gt; send this spreadsheet to several people each day. We've found that
gt; those with Excel 2003 cannot read one of my columns. That column
gt; contains a COUNTIF formula linking to another spreadsheet.
gt;
gt; When the 03 users attempt to open the file, they can see the contents
gt; of the cells I'm referring to above. However, when they answer the
gt; question about linking other files (whether they answer yes or no), the
gt; cells change to the #VALUE! error....
gt;
gt; Has anyone ever had this happen and/or do you know how to fix this?
gt;
gt;
gt; --
gt; telewats
gt; ------------------------------------------------------------------------
gt; telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
gt; View this thread: www.excelforum.com/showthread...hreadid=508308
gt;
gt;

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

quot;Kevin Bquot; gt; wrote in message
...
gt; CountIF isn't native to Excel, it's part of the add-in workbook
gt; Conditional
gt; Sum Wizard I believe. You can have your users check TOOLS/ADD INS and see
gt; if
gt; it is checked off, but I don't remember if that add-in was included in the
gt; 97
gt; version.
gt; --
gt; Kevin Backmann
gt;
gt;
gt; quot;telewatsquot; wrote:
gt;
gt;gt;
gt;gt; I have created a spreadsheet that has numerous links, formulas, etc.. I
gt;gt; send this spreadsheet to several people each day. We've found that
gt;gt; those with Excel 2003 cannot read one of my columns. That column
gt;gt; contains a COUNTIF formula linking to another spreadsheet.
gt;gt;
gt;gt; When the 03 users attempt to open the file, they can see the contents
gt;gt; of the cells I'm referring to above. However, when they answer the
gt;gt; question about linking other files (whether they answer yes or no), the
gt;gt; cells change to the #VALUE! error....
gt;gt;
gt;gt; Has anyone ever had this happen and/or do you know how to fix this?
gt;gt;
gt;gt;
gt;gt; --
gt;gt; telewats
gt;gt; ------------------------------------------------------------------------
gt;gt; telewats's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=30270
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=508308
gt;gt;
gt;gt;

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,quot;*gt;5 Days : 1234567*quot;)
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 1234589*quot;)
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 4561234*quot;)
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 6751678*quot;)

There is info on either side of gt;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
------------------------------------------------------------------------
telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
View this thread: www.excelforum.com/showthread...hreadid=508308There 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(quot;gt;5 daysquot;,
'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.
telewats wrote:
gt;
gt; I'm thinking maybe the problem is a combination of what everyone wrote.
gt; I have other formulas/macros all through my spreadsheet, and the values
gt; for these can be read by the Excel 2003 users; only the column that has
gt; =COUNTIF formulas cannot. So, assuming it's a problem with this
gt; particular formula, can someone tell me how to rewrite my formula so
gt; that it will give me the same info? Here's an example of the formula
gt; I'm currently using:
gt;
gt; =COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 1234567*quot;)
gt; COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 1234589*quot;)
gt; COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 4561234*quot;)
gt; COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 6751678*quot;)
gt;
gt; There is info on either side of gt;5 Days : xxxxxxx so I have to have the
gt; asterik. Can this be done with a SUMPRODUCT or another formula?
gt;
gt; Or, do you think it's a different problem altogether that I need to
gt; address?
gt;
gt; --
gt; telewats
gt; ------------------------------------------------------------------------
gt; telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
gt; View this thread: www.excelforum.com/showthread...hreadid=508308

--

Dave Peterson


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(quot;gt;5 daysquot;,'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
------------------------------------------------------------------------
telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
View this thread: www.excelforum.com/showthread...hreadid=508308Since 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,quot;*gt;5 Days : 1234567*quot;)
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 1234589*quot;)
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 4561234*quot;)
COUNTIF('O:\[FILEB.xls]REPORT 1'!$A:$A,quot;*gt;5 Days : 6751678*quot;)

Becomes:

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

sumproduct(--(isnumber(search(quot;gt;5 days : 1234589quot;,
'O:\[FILEB.xls]REPORT 1'!$A$1:$a$999)))

(and keep going.)

telewats wrote:
gt;
gt; Thanks for the tip. Two more questions: does this formula work with the
gt; asterisks? and how do I combine more than one criteria with this
gt; formula? In other words, after
gt; =sumproduct(--(isnumber(search(quot;gt;5 daysquot;,'O:\[FILEB.xls]REPORT
gt; 1'!$A$1:$a$999))) do I include the next portion by starting with
gt; ,(--(isnumber... or is this done some other way? I've tried a couple
gt; things already with no success.
gt;
gt; --
gt; telewats
gt; ------------------------------------------------------------------------
gt; telewats's Profile: www.excelforum.com/member.php...oamp;userid=30270
gt; View this thread: www.excelforum.com/showthread...hreadid=508308

--

Dave Peterson