Cell reference function

[millhouse123]

I have a formula =COUNTIFS($HA:$HA,HM7,$HB:$HB,HM8,$C:$C,1)

I want the second to last argument $C:$C to by dynamic. In other words I would like to reference a another cell which has the value C in it. By doing this I can simply drag the formulas across 1000 columns instead of manually updating it.


Thanks

 

[Scott Huish]

Why don't you just take the absolute reference off?

=COUNTIFS($HA:$HA,HM7,$HB:$HB,HM8,C:C,1)

Copy across.

 

[millhouse123]

I am sorry, I was very in clear what I am trying to do. I am looking for a formula like the one below, where Cell HM1 has a 3 in it, In my prior post I said it was a "C". I appreciate your assistance but this issue is beyond absolute vs relative formula reference.

The formula below gives me a #value error. When just looking at the Indirect portion it returns a 0.

=COUNTIFS($HA:$HA,HM7,$HB:$HB,HM8,INDIRECT(ADDRESS(1,HM1)&":"&ADDRESS(100000,HM1)),1)