MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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