Conditional formatting (top/bottom) for visible cells only

[rrandall]

This is my first post, I have been able to leverage prior posts for other questions, but I can't seem to find anything that addresses the following:

I am trying to use a top 5 / bottom 5 conditional reporting for data that has several regions and I would like to highlight the top/bottom 5 for only the visible cells when filtering by region.

Any thoughts?

 

[DonkeyOte]

Perhaps you can work with an approach along the lines of:

Excel Workbook
	A	B	C	D	E
1	Region	Value	Vis_Value	Top n	3
2	a	9	9	*	*
3	b	7	7	*	*
4	c	13	13	*	*
5	a	10	10	*	*
6	a	15	15	*	*
7	b	12	12	*	*
8	b	1	1	*	*
9	c	9	9	*	*
10	a	5	5	*	*
11	a	7	7	*	*
12	b	12	12	*	*
13	c	14	14	*	*
14	a	5	5	*	*
15	b	5	5	*	*
Sheet3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =COUNTIF($C$2:$C$15,">"&$C2)<$E$1	Abc

and the same with an "after" shot - ie to show the same but with filter applied:

<b>Sheet3</b><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:66px;" /><col style="width:64px;" /><col style="width:85px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Region</td><td >Value</td><td >Vis_Value</td><td >Top n</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffc000; ">a</td><td style="background-color:#ffc000; text-align:right; ">9</td><td style="background-color:#ffc000; text-align:right; ">9</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffc000; ">a</td><td style="background-color:#ffc000; text-align:right; ">10</td><td style="background-color:#ffc000; text-align:right; ">10</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffc000; ">a</td><td style="background-color:#ffc000; text-align:right; ">15</td><td style="background-color:#ffc000; text-align:right; ">15</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >a</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >a</td><td style="text-align:right; ">7</td><td style="text-align:right; ">7</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >a</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td><td >*</td><td >*</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >b</td><td style="text-align:right; ">5</td><td style="text-align:right; ">5</td><td >*</td><td >*</td></tr></table>

It follows that the same logic can be applied to bottom n values... and column C need not be visible of course.

 

[DonkeyOte]

ignore the * ... these equate to blank cells
(I had the audacity to preview the post at which point everything goes a little haywire)

note also that E1 is a variable - ie changing this number will alter how many values are highlighted - ie top n values.

 

[rrandall]

Smart and simple solution. Works perfectly and managing the # of items as a variable is win as well.

Thank you & cheers!