using cell value as part of range in conditional formatting formula

    Hi. As per the title, I am using the following formula:
    =IF(AND($U$15=3,COUNTIF($B$21:INDIRECT(Sheet2!D17),B21)>3),TRUE,FALSE)


    where Sheet2!D17 cell value = B33. Excel will not let me do this saying that I may not use reference operators. Is there another way around this? FYI the range in the CF formula needs to be dependant on the number of cells that have values in them.

    Re: using cell value as part of range in conditional formatting formula


    try changing the location of the indirect
    =IF(AND($U$15=3,COUNTIF(INDIRECT("$B$21:"&Sheet2!D17),B21)>3),TRUE,FALSE)

    ETAF

    Re: using cell value as part of range in conditional formatting formula


    Thanks etaf. Put range as text string in cell Sheet2!D19 and tweaked formula to


    =IF(AND($U$15=2,COUNTIF(INDIRECT(Sheet2!$D$19),B21)>2),TRUE,FALSE)


    which does the job.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login