Variable Text Reference Range

[Rubber Beaked Woodpecker]

Hi

In cell U6 I have the following formula;

=IF(AND(BC6<>""),IFERROR((COUNTIF(INDIRECT("'5.2'!$A2:AAA2"),"=1")),"0"),"")

Would it be possible so that the text reference range would be dependable on what is entered in cell Z1.

For example Z1 = $B2:BBB2

Then the formula in U6 would become;

=IF(AND(BC6<>""),IFERROR((COUNTIF(INDIRECT("'5.2'!$B2:BBB2"),"=1")),"0"),"")

Many thanks,

RBW

 

[kvsrinivasamurthy]

Try

=IF(AND(BC6<>""),IFERROR((COUNTIF(INDIRECT("'5.2'!"&Z1),"=1")),"0"),"")

 

[Rubber Beaked Woodpecker]

Try

=IF(AND(BC6<>""),IFERROR((COUNTIF(INDIRECT("'5.2'!"&Z1),"=1")),"0"),"")

This work great thank you.