Another Spin to Updating Links

[Mike]

Good Morning All,
I posted yesterday in regards to a workbook that does not display the
updated values from linked workbooks. Dave Peterson replied that I use the
=SUMPRODUCT formula instead of the =COUNTIF formula, which did the trick.
However I have run into a problem when I want to find a zero in one of the
linked workbooks. In one of the linked workbooks I have the years 2000 to
2010 located in $A$1:$A:11, in which I have data in from $B$1:$B:$6 (which
are the years 2000 to 2005). The remaining cells ($B$7:$B:$11 are blank).
I want to find the number of occurences of 0's in $B$1:$B$11. It counts the
actual 0's but it also counts the empty cells in $B$7:$B$11 as 0's.
Here's an example:

Workbook years.xls
A B
-----------------------
1 2000 47
2 2001 16
3 2002 7
4 2003 0
5 2004 3
6 2005 5
7 2006
8 2007
9 2008
10 2009
11 2010

In the formula in the orginal workbook:
=SUMPRODUCT(--('C:\My Documents\excel\[years.xls]Sheet1'!$B$1:$B$11=0)) it
returns the value of 6 (it counts the 0 in $B4 and also counts the 0's in
the blank cells of $B7:$B11. I just want it to count the 0's in the
nonblank cells of $B1:$B11. I added
to the rest of the formula : -(COUNTBLANK('C:\My
Documents\excel\[years.xls]Sheet1'$B$1:$B$11), which then gives the correct
value of 1. But when I start up the orginal workbook and when it aks to
update the data it returns the #VALUE! (in another words it does not display
the updated value) because of the COUNTBLANK formula. Is there anyway to
fix the formula that it doesn't count the blanks as zero's and also updates
the external links and displays the data.
Hope this is not too complicated...any help would be very much appreciated.

Mike

 

[Dave Peterson]

This style worked ok for me:

=SUMPRODUCT(--(A1:A10=0),--ISNUMBER(A1:A10))

You'll add the drive\path\filename\worksheet stuff???

Good Morning All,
I posted yesterday in regards to a workbook that does not display the
updated values from linked workbooks. Dave Peterson replied that I use the
=SUMPRODUCT formula instead of the =COUNTIF formula, which did the trick.
However I have run into a problem when I want to find a zero in one of the
linked workbooks. In one of the linked workbooks I have the years 2000 to
2010 located in $A$1:$A:11, in which I have data in from $B$1:$B:$6 (which
are the years 2000 to 2005). The remaining cells ($B$7:$B:$11 are blank).
I want to find the number of occurences of 0's in $B$1:$B$11. It counts the
actual 0's but it also counts the empty cells in $B$7:$B$11 as 0's.
Here's an example:

Workbook years.xls
A B
-----------------------
1 2000 47
2 2001 16
3 2002 7
4 2003 0
5 2004 3
6 2005 5
7 2006
8 2007
9 2008
10 2009
11 2010

In the formula in the orginal workbook:
=SUMPRODUCT(--('C:\My Documents\excel\[years.xls]Sheet1'!$B$1:$B$11=0)) it
returns the value of 6 (it counts the 0 in $B4 and also counts the 0's in
the blank cells of $B7:$B11. I just want it to count the 0's in the
nonblank cells of $B1:$B11. I added
to the rest of the formula : -(COUNTBLANK('C:\My
Documents\excel\[years.xls]Sheet1'$B$1:$B$11), which then gives the correct
value of 1. But when I start up the orginal workbook and when it aks to
update the data it returns the #VALUE! (in another words it does not display
the updated value) because of the COUNTBLANK formula. Is there anyway to
fix the formula that it doesn't count the blanks as zero's and also updates
the external links and displays the data.
Hope this is not too complicated...any help would be very much appreciated.

Mike

 

[Mike]

I want to thank you Dave for all your help. Works great!!
Mike

This style worked ok for me:

=SUMPRODUCT(--(A1:A10=0),--ISNUMBER(A1:A10))

You'll add the drive\path\filename\worksheet stuff???

Good Morning All,
I posted yesterday in regards to a workbook that does not display the
updated values from linked workbooks. Dave Peterson replied that I use
the
=SUMPRODUCT formula instead of the =COUNTIF formula, which did the trick.
However I have run into a problem when I want to find a zero in one of
the
linked workbooks. In one of the linked workbooks I have the years 2000
to
2010 located in $A$1:$A:11, in which I have data in from $B$1:$B:$6
(which
are the years 2000 to 2005). The remaining cells ($B$7:$B:$11 are
blank).
I want to find the number of occurences of 0's in $B$1:$B$11. It counts
the
actual 0's but it also counts the empty cells in $B$7:$B$11 as 0's.
Here's an example:

Workbook years.xls
A B
-----------------------
1 2000 47
2 2001 16
3 2002 7
4 2003 0
5 2004 3
6 2005 5
7 2006
8 2007
9 2008
10 2009
11 2010

In the formula in the orginal workbook:
=SUMPRODUCT(--('C:\My Documents\excel\[years.xls]Sheet1'!$B$1:$B$11=0))
it
returns the value of 6 (it counts the 0 in $B4 and also counts the 0's in
the blank cells of $B7:$B11. I just want it to count the 0's in the
nonblank cells of $B1:$B11. I added
to the rest of the formula : -(COUNTBLANK('C:\My
Documents\excel\[years.xls]Sheet1'$B$1:$B$11), which then gives the
correct
value of 1. But when I start up the orginal workbook and when it aks to
update the data it returns the #VALUE! (in another words it does not
display
the updated value) because of the COUNTBLANK formula. Is there anyway to
fix the formula that it doesn't count the blanks as zero's and also
updates
the external links and displays the data.
Hope this is not too complicated...any help would be very much
appreciated.

Mike