Indirect and Sheet Name


R

Rob

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
 
Ad

Advertisements

B

Bernard Liengme

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
 
T

tompl

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
 
R

Roger Govier

Hi Rob

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

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

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top