Syntax problem
Posted by jean-michel verheugen on May 04, 2001 1:28 AM
hello,
In a cell, I have the following formula
=COUNTIF(TEST!$C$1:$C$400;"<5")
I write the string TEST in the cell D4 (TEST is the name of a worksheet)
In the above formula, I would like to specify the coordonates of the cell containing TEST instead of the string itself
I've try the followings
=COUNTIF(D4!$C$1:$C$400;"<5") he asks me for a D4 file
=COUNTIF('D4'!$C$1:$C$400;"<5") he asks me for a D4 file
=COUNTIF(+D4!$C$1:$C$400;"<5") Error
=COUNTIF((+D4)!$C$1:$C$400;"<5") Error
What 's the exact syntax ?
thank for your help
jm
Posted by Aladin Akyurek on May 04, 2001 2:49 AM
Jean-Michel
Try:
=COUNTIF(INDIRECT(D46&"!"&"$C$1:$C$400"))
If the formulas that you use are on a sheet in the same workbook as TEST, I'd suggest using a name for the range of interest.
In order to do that, select the range C1:C400, give it a name (e.g., DATA) via the Name Box or via the option Insert|Name|Define.
Now you can use the name in your formulas, e.g.,
=COUNTIF(DATA,"<5") [ Adapt it to the Dutch version you apparently use ]
Addionally, I'd put that the condition value 5 in a cell of its own and name that cell CRIT via the Name Box. Your formulas must change accordingly:
=COUNTIF(DATA,"<"&CRIT)
Groeten.
Aladin
Posted by Jean-Michel Verheugen on May 07, 2001 5:30 AM
Aladin,
you solved my problem
thanks a lot.
jm
