cell reference in formula
 

This is going to be very simple, I just know it, but I am stuck in a
mind-trap

A working formula which returns a count:
"=COUNTA(2:2)"
This would return something like "27", a count of cells in row 2 with
data, as does"
"=COUNTA($2:$2)"
"=COUNTA(MySheet!$2:MySheet!$2)"

Now I want to have a cell which I enter a "row number" for my formula
to count, i.e 2,3,4, etc

I would like to have something like
"=COUNTA((B5):(B5))"
"=COUNTA($(B5):$(B5))"
"=COUNTA(MySheet!$(B5):MySheet!$(B5))"

..... work, but it does not, and I can see why is does not. I just can't
figure out how to make it work.

Like I said this should be simple, I'm just stuck at the moment

Hi
try
=COUNTA(INDIRECT(B5 & ":" & B5))

--
Regards
Frank Kabel
Frankfurt, Germany

Michael Dwyer wrote:
This is going to be very simple, I just know it, but I am stuck in a
mind-trap

A working formula which returns a count:
"=COUNTA(2:2)"
This would return something like "27", a count of cells in row 2 with
data, as does"
"=COUNTA($2:$2)"
"=COUNTA(MySheet!$2:MySheet!$2)"

Now I want to have a cell which I enter a "row number" for my formula
to count, i.e 2,3,4, etc

I would like to have something like
"=COUNTA((B5):(B5))"
"=COUNTA($(B5):$(B5))"
"=COUNTA(MySheet!$(B5):MySheet!$(B5))"

.... work, but it does not, and I can see why is does not. I just
can't figure out how to make it work.

Like I said this should be simple, I'm just stuck at the moment

One way

=COUNTA(OFFSET(A1,B5-1,,,256))

You can also use INDIRECT

Regards,

Peo Sjoblom

"Michael Dwyer" wrote:

This is going to be very simple, I just know it, but I am stuck in a
mind-trap

A working formula which returns a count:
"=COUNTA(2:2)"
This would return something like "27", a count of cells in row 2 with
data, as does"
"=COUNTA($2:$2)"
"=COUNTA(MySheet!$2:MySheet!$2)"

Now I want to have a cell which I enter a "row number" for my formula
to count, i.e 2,3,4, etc

I would like to have something like
"=COUNTA((B5):(B5))"
"=COUNTA($(B5):$(B5))"
"=COUNTA(MySheet!$(B5):MySheet!$(B5))"

..... work, but it does not, and I can see why is does not. I just can't
figure out how to make it work.

Like I said this should be simple, I'm just stuck at the moment

If you enter your row number in cell B1, then

=COUNTA(INDEX(1:65536,B1,1):INDEX(1:65536,B1,256))

will give you the intended result. This solution avoids
the INDIRECT() and the OFFSET() functions which are
volatile (see
http://www.whooper.co.uk/excelstuff.htm#tip1
).

HTH,
Bernd

Bernd Plumhoff wrote...
If you enter your row number in cell B1, then

=COUNTA(INDEX(1:65536,B1,1):INDEX(1:65536,B1,256) )

will give you the intended result. This solution avoids the INDIRECT()
and the OFFSET() functions which are volatile . . .
....

Good point, but why use two INDEX calls? Why not

=COUNTA(INDEX(1:65536,B1,0))

?

Hi Harlan,

Better point. Thanks.

Regards,
Bernd