question about formula based on another workbook info

T

Treesy

I am trying to use the CountIF function in Workbook A that counts info in a
range of cells located in Workbook B. I'd like Worksheet A to update when
changes to Worksheet B is made. But the function only works if Workbook B is
open, otherwise I get the dreaded #VALUE! error. What am I doing wrong?

Thanks!!
 
K

Kevin B

Why don't you place the Cound function in workbook B itself, and then link to
the cell that has the count formula from Column A. That way the workbook
will not need to be opened for formula to calculate.

Or you could save Workbook A and Workbook B as a workspace and then open the
workspace when you need to.
 
D

Don Guillett

try it this way with the path, etc
=COUNTIF('C:\yourfolder\[yourfilename.xls]yoursheet'!$B$5:$B$9,"criteria")
 
D

Don Guillett

I should have said
=SUMPRODUCT(('C:\yourfolder\[yourfilename.xls]yourworksheet'!$B$5:$B$9="SF")*1)
 
T

Treesy

Worksheet B is a list of applications sold in a given month and each
application has an employee name for who sold the application. Worksheet A
lists all the employees and I'd like it to count how many times it sees that
employee's name on Worksheet B. There really isn't room on Worksheet B to
list all the employees and since all the employees are listed on Worksheet A,
I thought I could do the function there. I'm going to try Don Guillett's
suggestion.

Thanks for the help!!
 
T

Treesy

Thanks, that worked perfectly!!!

Don Guillett said:
I should have said
=SUMPRODUCT(('C:\yourfolder\[yourfilename.xls]yourworksheet'!$B$5:$B$9="SF")*1)

--
Don Guillett
SalesAid Software
[email protected]
Treesy said:
I am trying to use the CountIF function in Workbook A that counts info in a
range of cells located in Workbook B. I'd like Worksheet A to update when
changes to Worksheet B is made. But the function only works if Workbook B
is
open, otherwise I get the dreaded #VALUE! error. What am I doing wrong?

Thanks!!
Click to expand...
Click to expand...
 
D

Don Guillett

glad to help

--
Don Guillett
SalesAid Software
[email protected]
Treesy said:
Thanks, that worked perfectly!!!

Don Guillett said:
I should have said
=SUMPRODUCT(('C:\yourfolder\[yourfilename.xls]yourworksheet'!$B$5:$B$9="SF")*1)

--
Don Guillett
SalesAid Software
[email protected]
Treesy said:
I am trying to use the CountIF function in Workbook A that counts info
in a
range of cells located in Workbook B. I'd like Worksheet A to update
when
changes to Worksheet B is made. But the function only works if
Workbook B
is
open, otherwise I get the dreaded #VALUE! error. What am I doing
wrong?

Thanks!!
Click to expand...
Click to expand...
Click to expand...
 
Top