Prev: "AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HEL
Next: Calculating Resolution Rates
From: Rob on 23 Mar 2010 17:38
I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a
range which is defined in cell A3, the formula in A3 is below. Is there a
way of not having to include the sheet name (Sheet 1) in cell A3 and to
include in the COUNTA formula.

="'Sheet 1'"&"!C"&A1&":"&"C"&B1

Thanks, Rob


From: Bernard Liengme on 23 Mar 2010 18:05
Two possibilities
a) replace the A3 formula by ="C"&A1&":"&"C"&B1
and replace the COUNTA by =COUNTA(INDIRECT("'Sheet1'!"&A3))

or, better still
b) do away with the the A3 formula all together and use
=COUNT(INDIRECT("'Sheet1'"&"!C"&A1&":"&"C"&B1))

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme


"Rob" <anonymous(a)discussions.microsoft.com> wrote in message
news:eARa7EtyKHA.2552(a)TK2MSFTNGP04.phx.gbl...
> I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in
> a range which is defined in cell A3, the formula in A3 is below. Is there
> a way of not having to include the sheet name (Sheet 1) in cell A3 and to
> include in the COUNTA formula.
>
> ="'Sheet 1'"&"!C"&A1&":"&"C"&B1
>
> Thanks, Rob
>
From: tompl on 23 Mar 2010 18:35
If you leave out the sheet name then the formula will apply only to the
current sheet and it would look like this: ="C"&A1&":"&"C"&B1.

Or, you could ignore cell A3 and use a formula like this:
=COUNTA(INDIRECT("C" & A1 & ":C" & B1))

Tom

"Rob" wrote:

> I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a
> range which is defined in cell A3, the formula in A3 is below. Is there a
> way of not having to include the sheet name (Sheet 1) in cell A3 and to
> include in the COUNTA formula.
>
> ="'Sheet 1'"&"!C"&A1&":"&"C"&B1
>
> Thanks, Rob
>
>
> .
>
From: Roger Govier on 24 Mar 2010 04:40
Hi Rob

Another alternative, would be to use Index rather than the volatile
Indirect function

=COUNTA(INDEX(C:C,A1):INDEX(C:C,B1))

--
Regards
Roger Govier

Rob wrote:
> I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items in a
> range which is defined in cell A3, the formula in A3 is below. Is there a
> way of not having to include the sheet name (Sheet 1) in cell A3 and to
> include in the COUNTA formula.
>
> ="'Sheet 1'"&"!C"&A1&":"&"C"&B1
>
> Thanks, Rob
>
>
From: Rob on 24 Mar 2010 16:56
Thanks everyone, lots of optiosn to try out.

Regards, Rob

"Bernard Liengme" <bliengme(a)TRUENORTH.stfx.ca> wrote in message
news:ufz7wTtyKHA.928(a)TK2MSFTNGP05.phx.gbl...
> Two possibilities
> a) replace the A3 formula by ="C"&A1&":"&"C"&B1
> and replace the COUNTA by =COUNTA(INDIRECT("'Sheet1'!"&A3))
>
> or, better still
> b) do away with the the A3 formula all together and use
> =COUNT(INDIRECT("'Sheet1'"&"!C"&A1&":"&"C"&B1))
>
> best wishes
> --
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
>
>
> "Rob" <anonymous(a)discussions.microsoft.com> wrote in message
> news:eARa7EtyKHA.2552(a)TK2MSFTNGP04.phx.gbl...
>> I have a formula =COUNTA(INDIRECT(A3)) which returns the count of items
>> in a range which is defined in cell A3, the formula in A3 is below. Is
>> there a way of not having to include the sheet name (Sheet 1) in cell A3
>> and to include in the COUNTA formula.
>>
>> ="'Sheet 1'"&"!C"&A1&":"&"C"&B1
>>
>> Thanks, Rob
>>


 | 
Pages: 1
Prev: "AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HEL
Next: Calculating Resolution Rates