conditional formattinggoogle sheets
I have a google sheet with 1006 rows which I am trying to use conditional formatting to identify the two top values across 7 columns (J to P) for each row.
I managed to get 1 row to work (highlighted in green) but I cannot replicate the conditional formatting across the other 1 005 rows.
The formula I used on row 9 was
=ARRAYFORMULA(OR(J9=LARGE($J$9:$P$9,{1,2})))
I think I was trying to achieve the result across spreadsheets before (rather than sheets). If you put a number in A1 of SheetB and in A1 of SheetA Conditional formatting... like so:
then if you keep adding 1 to the value in SheetB!A1 the fill colours in SheetA!A1 should toggle red/pale blue.
The formulae I chose are of the type:
=iseven(indirect("SheetB"&"!A1"))
FORMULAS:
RED:
=if(And(COUNTA(B42:H42)>1,isblank(D42)),true,false)
YELLOW
=if(and(istext(D42),(Or(isblank(B42),isblank(C42),isblank(E42),isblank(F42),isblank(H42)))),true,false)
GREEN
=if(counta(B42:H42)=7,true,false)
Best Answer
Instead of
use
The above reference make referred columns to be absolute but rows are relative