#NAME! Error

[STEDIX]

I have two separate workbooks on different network drives. One is a SHARED
workbook and one is not. What I am doing is using a COUNTIF formual to bring
counts from the Shared workbook to the other workbook. I can get an accurate
value. However, when I close the source workbook, I get the #NAME! Error in
the field with the formula. When I reopen the source workbook and update the
other workbook the #NAME! error goes away and the correct value appears.

Any help would be apppreceiated. Here is the formula:

=COUNTIF('[Safety Meeting Resource Library for Branch and Driver
Meetings.xls]Fargo'!$B$10:$B$134,"April-09")

 

[Dave Peterson]

You sure you don't get a #Value! error?

There are functions that don't work when the "sending" workbook is closed.

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

But there are workarounds:

=sumproduct(--('[yourfilename]Fargo'!$B$10:$B$134="April-09"))

If those cells were really dates:

=sumproduct(--(text('[yourfilename]Fargo'!$B$10:$B$134,"yyyymm")="200904"))


Build the formula with your workbooks open and let excel worry about the syntax
when you close the sending workbook.



Adjust the ranges to match--but you can't use whole columns (except in xl2007).

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

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

I have two separate workbooks on different network drives. One is a SHARED
workbook and one is not. What I am doing is using a COUNTIF formual to bring
counts from the Shared workbook to the other workbook. I can get an accurate
value. However, when I close the source workbook, I get the #NAME! Error in
the field with the formula. When I reopen the source workbook and update the
other workbook the #NAME! error goes away and the correct value appears.

Any help would be apppreceiated. Here is the formula:

=COUNTIF('[Safety Meeting Resource Library for Branch and Driver
Meetings.xls]Fargo'!$B$10:$B$134,"April-09")

 

[Gary''s Student]

The error is telling you that with the Shared workbook closed, COUNTIF()
cannot retrieve its data. An easy workaround is to create a local table of
links to the Shared workbook and then use COUNTIF() on the local copy.

 

[FSt1]

hi
you would have to have the entire file path in the formula in order for the
formula to work while the source workbook is closed. here is an example of
one i use.

='C:\Documents and Settings\FSt1\My Documents\XL\[BCPlan.xls]Expences'!$G$2

note the apostrophes.
the formula you are using now can find the source workbook while it's in
memory but once closed, you need the file path also.

Regards
FSt1