Change column reference within a range by numeric position within the range

Hi

I'm looking for a way to change the column reference in a formula by translating a numeric value into the column reference position in a range. For example;

=COUNTIF($O:$O,"Yes")

I also have a column which contains the relative position of the column used in the above formula, so that column O would equate to a value of 1. What I want to do is alter the column reference in the formula by the numerci value in a cell, when copied down, so that if cell value = 2 the effect of the formula would be =COUNTIF($P:$P,"Yes"), and if cell value = 3, =COUNTIF($Q:$Q,"Yes") etc. etc.

I guess this would almost be a reverse of the COLUMN function, where a numeric value is translated into a column address.

Thanks in advance

this would be INDIRECT
I'd personally use it here together with CHR function. If your cell containing "how many columns right from O:O" is A1:

Please Login or Register  to view this content.

of course it works only until Z column

If its too little - use ADDRESS to get column name

Lovely stuff.

Tried both zbor and martindwilson's methods, and they both worked perfectly. Sorry Kaper, I didn't try yours (although I'm sure it works too), purely becuase the formual string was longer than the other 2.

Many thanks