Using range name typed in cell to reference array

L

Landmine

I have defined ranges of cells with names (ie TCO71, TCO72). I have a column
of data 71, 72 ... I am trying to use a function ie count to use this name.
I tried the following with mixed results

=count(TCO71) gave correct result
=count(a3) with TCO71 typed in A3 gave a zero result
=count(indirect(A3)) gave a zero result
=count(concatenate("TCO",text(B3,0)) gave a zero result

in reality I would like to use a form similar to the last line so I do not
have to add another column with the range name.

Any help would be appreciated
Landmine
 
A

Aladin Akyurek

Landmine said:
I have defined ranges of cells with names (ie TCO71, TCO72). I have
column
of data 71, 72 ... I am trying to use a function ie count to use thi
name.
I tried the following with mixed results

=count(TCO71) gave correct result
=count(a3) with TCO71 typed in A3 gave a zero result
=count(indirect(A3)) gave a zero result
=count(concatenate("TCO",text(B3,0)) gave a zero result

in reality I would like to use a form similar to the last line so I d
not
have to add another column with the range name.

Any help would be appreciated
Landmine
Click to expand...

[2] is the wrong idiom considering your intent.

[3] should give you the same result as [1].

Unclear where you're heading with [4] since you did not specify th
value B3 is supposed to house
 
B

Bernard Liengme

To reinforce what Aladin has told you:
=count(indirect(A3)) should work if A3 has the correct name of a range
Not just from memory - I have just double checked it by experiment

best wishes
 
L

Landmine

I apologize for my lack of detail. In column A I typed the Range names (TCO
71, TCO72 etc). I did not change the cell formatting. In column B I typed
just the numbers 71, 72, etc. On another sheet in the workbook I have a
column of data that I have associated to the range names(TCO71, TCO72 etc).
I would like to return the number of items, average, Standard deviation etc
of each range name. Only one thing I tried worked, but this is not as
efficient when creating the sheet as the number of range names each large and
the number of items I want returned is large.

This formula works:

=COUNT(TCO71) It returns the correct quantity
The following do not.
=COUNT(A4) Where I typed TCO71 in cell A4. It returns 0.
=COUNT(INDIRECT(A4)) Also returns 0.
What I would actually like is to concatenate TCO with the number in column
B. To form TCO71, TCO72 etc.
=COUNT(CONCATENATE("TCO",B4)). It returned a 0 also.

Any help would be appreciated.

Thanks

Aladin Akyurek said:
I have defined ranges of cells with names (ie TCO71, TCO72). I have a
column
of data 71, 72 ... I am trying to use a function ie count to use this
name.
I tried the following with mixed results

=count(TCO71) gave correct result
=count(a3) with TCO71 typed in A3 gave a zero result
=count(indirect(A3)) gave a zero result
=count(concatenate("TCO",text(B3,0)) gave a zero result

in reality I would like to use a form similar to the last line so I do
not
have to add another column with the range name.

Any help would be appreciated
Landmine
Click to expand...

[2] is the wrong idiom considering your intent.

[3] should give you the same result as [1].

Unclear where you're heading with [4] since you did not specify the
value B3 is supposed to house.
Click to expand...
 
Top