Conditional Formatting

[nschepp]

Hey All,

Is there a way around what I am trying to do?

I have a 2nd sheet which copies cells from the First Sheet, but as you know, when there is nothing in the first cell, it represents a "0".

I have a Top/Bottom 10 Conditional format applied to the cells, but it applies to the "0" cells also! (my only work around at the moment is to continually add the conditional formatting as each day passes).

Is there a way to have the conditional format applied so that it does a top 10 excluding "0" cells?

Many thanks. Sample attached.

[sktneer]

Try these formula for conditional formatting........

For Bottom 10...

=AND(B3>=SMALL(IF($B$3:$B$44<>0,$B$3:$B$44),1),B3<=SMALL(IF($B$3:$B$44<>0,$B$3:$B$44),10))

For Top 10...

=AND(B3<=LARGE(IF($B$3:$B$44<>0,$B$3:$B$44),1),B3>=LARGE(IF($B$3:$B$44<>0,$B$3:$B$44),10))