how to dynamically reference a cell

[vladi305]

Ok I have the following formula: =COUNTIF(Data!B$1:F$30,[@Ball])

I want to from a drop down menu change the range of the F cell to 40, 60 or any range

=COUNTIF(Data!B$1:F$& C2 ,[@Ball])
=COUNTIF(Data!B$1:F$&"C2",[@Ball])

I tried but I keep getting errors

Thanks in advance

 

[Fluff]

How about

=COUNTIF(Data!B$1:INDEX(Data!B$:F$, C2) ,[@Ball])

 

[vladi305]

How about

=COUNTIF(Data!B$1:INDEX(Data!B$:F$, C2) ,[@Ball])

I get this:

 

[vladi305]

How about

=COUNTIF(Data!B$1:INDEX(Data!B$:F$, C2) ,[@Ball])

I get this error

 

[Fluff]

Oops, it should be

=COUNTIF(Data!B$1:INDEX(Data!F:F, C2) ,[@Ball])

 

[vladi305]

Oops, it should be

=COUNTIF(Data!B$1:INDEX(Data!F:F, C2) ,[@Ball])

Amazing, Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.