using content of a cell in a formula in another cell

[Guest]

I am trying to use the value on one cell to form the range part an a countif
formula in another cell like this:
=COUNTIF(Actions!I2:I&"C2","*all*")

where C2 is the cell in the current worksheet whose contents (currently 20)
I want to use. The contents will change periodically. The formula that I
want evaluated would look like:
=COUNTIF(Actions!I2:I20,"*all*")

Anyone have a suggestion, please?

 

[Jason Morin]

=COUNTIF(INDIRECT("Actions!I2:I"&C2),"*all*")

or

=COUNTIF(OFFSET(I2,,,C2-1),"*all*")

HTH
Jason
Atlanta, GA

 

[Guest]

A couple of ways

=COUNTIF(OFFSET(Actions!$I$2,,,C2+1,),"*all*")

=COUNTIF(INDEX(Actions!I2:I10000,):INDEX(Actions!I2:I10000,C2),"*all*")

the latter is not volatile although a bit longer, it can also be written as

=COUNTIF(INDEX(Actions!I:I,2):INDEX(Actions!I:I,C2+2),"*all*")

where the 2 and +2 indicates the range starts in row 2 of column I

Regards,

Peo Sjoblom

 

[Guest]

Jason, Peo:
All your suggestions work great. Thank you!!