Conditional Formatting - formula for adjacent cells

Hi "Golf",

Here is another attempt to define the names for the calculations. Main reason that allows us to avoid array formulas which don't work with conditional formatting (more exactly significantly simplify the formulas).

All names you may find and edit if necessary in Formulas->Name Manager

First define cells where our ranges starts and ranges itself

// Cell on the top of Sales section
OnSales
=Sheet1!$A$1 

// Cell on the top of Admin section
OnAdmin
=Sheet1!$A$10

// Ranges with scores by sections
SalesScore1
=Sheet1!$C$2:$C$9

//similar for SalesScore2, AdminScore1, AdminScore2
// and the entire score ranges
Score1
=Sheet1!$C$2:$C$18
Score2
=Sheet1!$E$2:$E$18

and name for the condition which rows to exclude

// With this name we define condition which rows to exclude finding
// largest Score 1

RowsToExclude
=IF(
(ROW(Score1)<>(MATCH(LARGE(SalesScore2,1),SalesScore2,0)+ROW(OnSales)))*
(ROW(Score1)<>(MATCH(LARGE(SalesScore2,2),SalesScore2,0)+ROW(OnSales)))*
(ROW(Score1)<>(MATCH(LARGE(SalesScore2,3),SalesScore2,0)+ROW(OnSales)))*
(ROW(Score1)<>(MATCH(LARGE(AdminScore2,1),AdminScore2,0)+ROW(OnAdmin)))*
(ROW(Score1)<>(MATCH(LARGE(AdminScore2,2),AdminScore2,0)+ROW(OnAdmin)))*
(ROW(Score1)<>(MATCH(LARGE(AdminScore2,3),AdminScore2,0)+ROW(OnAdmin)))
,Score1)

With above names we may find values for first three largest scores

Large 1:	23	=LARGE(RowsToExclude,1)
// To simplify a bit define one more name
CountLarge1
=SUMPRODUCT((--RowsToExclude>0)*(Score1=LARGE(RowsToExclude,1)))

// and with help of it find next largests
Large 2:	18	=LARGE(RowsToExclude,1+CountLarge1)
Large 3:	17	=LARGE(RowsToExclude,1+ CountLarge1+
               SUMPRODUCT((--RowsToExclude>0)*(Score1=LARGE(RowsToExclude,1+CountLarge1))))

With this formulas we define conditional formatting rules checking if cell value = LargeN and cell row is not match the row for one of largest in Score 2

// First large conditional formatting rule 
=($C2=LARGE(RowsToExclude,1))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,1),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,2),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,3),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,1),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,2),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,3),AdminScore2,0)+ROW(OnAdmin)))

// Second large conditional formatting rule 
=($C2=LARGE(RowsToExclude,1+CountLarge1))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,1),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,2),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,3),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,1),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,2),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,3),AdminScore2,0)+ROW(OnAdmin)))

// Third large conditional formatting rule 
=($C2=LARGE(RowsToExclude,1+ CountLarge1+
               SUMPRODUCT((--RowsToExclude>0)*(Score1=LARGE(RowsToExclude,1+CountLarge1)))))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,1),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,2),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(SalesScore2,3),SalesScore2,0)+ROW(OnSales)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,1),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,2),AdminScore2,0)+ROW(OnAdmin)))*
 (ROW($C2)<>(MATCH(LARGE(AdminScore2,3),AdminScore2,0)+ROW(OnAdmin)))

And in attached file