Countif function on worksheets on different directory

[hotdogg1986]

Hi,

I'm trying to use a countif function to compare data input dates, something to count the frequency of data inputted that month. The two excel files are located on different directory on a network drive.

I tried to use a function like this...
=COUNTIF('[test.xls]sheet1'!$A$14:$A$400,">="&C44)-COUNTIF('[test.xls]sheet1'!$A$14:$A$400,">="&C45)
where C44 and C55 are the dates, somethign like 01-Mar-09 to 31-Mar-09

so if there are values in test.xls sheet1 say,

28-Feb-09 xxx
01-Mar-09 xxx
15-Mar-09 xxx
20-Mar-09 xxx

I'll get a value of 3.


The problem is, this only works when the test.xls sheet is opened. Once I update the other sheet with the autoupdate prompt, they all becomes #value!. This also happens if I close the test.xls and then refresh the function. Anybody got a way around this problem?

Thanks for the help.
FC <!-- / message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_sig --><!-- END TEMPLATE: ad_showthread_firstpost_sig -->

 

[nbrcrunch]

you could convert the formulas to static values if that will work for you.

Select the column or row (or even just a cell or group of cells).
Right click choose copy
Right click again, choose Paste-Special. On next screen, select "values" click ok.

 

[hotdogg1986]

you could convert the formulas to static values if that will work for you.

Select the column or row (or even just a cell or group of cells).
Right click choose copy
Right click again, choose Paste-Special. On next screen, select "values" click ok.

I kind of need the values to be updated... because I'm linking several sheets together and using a summary sheet to keep track of the data inputed in the other sheets. Thanks for the reply tho