Excel 2002 Formula Problem

[mulr1966]

Hello everyone , I am having a problem with External References for
formula.

Why does the first Example work and the second formula not ?


Example 1: =COUNTIF('[March_2004_ts.xls]Marc
2004'!$AM$26:$AM$200,"Cardiac")

Example 2:
COUNTIF('[C:\REPORTS\XLS_SHEETS\March_2004_ts.xls]Marc
2004'!$AM$26:$AM$200,"Cardiac")

The reason I ask is because I have spent many hours setting up a QTRL
Report with Substitution Variables for a macro to use and need to ente
the exact path to the Spreadsheets as a Variable.
any help on a work around would be appreciated.
Thank You
Rober

 

[mulr1966]

PS I noticed that If I open the workbook March_2004_ts the formul
completes the calculation.
I need to be able to do the calulations on may workbooks that are i
the directory
so opening them then closing them as part of the macro maybe the answe
I am not sure
This seems like a pretty lengthy / slow process

 

[Frank Kabel]

Hi
change the second formula to
COUNTIF('C:\REPORTS\XLS_SHEETS\[March_2004_ts.xls]March
2004'!$AM$26:$AM$200,"Cardiac")

 

[mulr1966]

Thank you Frank,
Still the returned value of that formula is equal to #VALUE! becaus
the workbook was closed.

I have approx 75 workbooks each containing a single sheet which i
labled similar to the name of the workbook
for Example March_2004_ts.xls has a Worksheet called "March 2004" ...
(no quotes) ...
the Report page I have has about 100 different fomulas , some Countif
Sum , Sumproduct etc ...

I have edited the formulas to look as suc
$$=Countif('[$$PATH\[$$FILENAME]$$SHEETNAME'!$AM$26:$AM$200,"Cardiac")


I tested a macro that would change the values of the Variables, Th
last one being the variable to change the $$=COUNTIF to an Actua
Fomula =Countif (this was done to avoid External Reference Errors),
still get #VALUE! as the result. Not the actual Answer which would b
some number...

I then opened the file March_2004_ts.xls .... Once I did this th
#Value! was changed to the correct Numerical Value. Is there a way t
not have to open the March_2004_ts.xls File to get the correc
numerical value?
Thank you again for your assistance
Rober

 

[Frank Kabel]

Hi
is it only the COUNTIF function? Some of Excel's functions won't work
on cloded workbooks try in your example:
=SUMPRODUCT(--('PATH\[FILENAME.XLS]SHEETNAME'!$AM$26:$AM$200="Cardiac")
)