CODEWITHSUNDEEP
filtergoogle-sheetsgoogle-sheets-formulags-conditional-formatting
Parker Frank B
Parker Frank B

Reputation: 33

Conditional Formatting based on Another Range

I want to set conditional formatting on a sheet with range A2:D15 using a custom formula that changes the cell background color. I have column F which includes a list of names (F2:F13), and column H which includes what class the name is (G2:G13). I want to compare each row by saying that if the class in G2 = "Paladin" and F2 is not blank, then perform the conditional formatting. I want this to span all 12 rows in F and G, but I cannot pass an array using an if function.

Example sheet: https://docs.google.com/spreadsheets/d/1a32ItT0HpRsov_oG5-CVHVe3HZV9WP-LypkxugsoK0g/edit?usp=sharing

I tried using this formula: 

=if(and(not(isblank(F2)),G2="Paladin"),1)

It successfully changes the first result in my range because it happens to be true, but I need it to include the entire array, so I tried using this: 

=if(and(not(isblank(F2:F13)),G2:G13="Paladin"),1)

Also played around with this =if(and(F2=A2,G2="Paladin"),1) - same problem I reckon, but more accurate if I could find a way to use arrays.

However, IF function as I understand it cannot evaluate arrays. I tried using $ signs to play around with it, similar to this example I found: https://www.benlcollins.com/formula-examples/array-formula-intro/ - but that is using numerical data and when I use $ it either applies the conditional formatting on the entire row, or entire column, or the entire range of A3:D16.

Upvotes: 0

Views: 290

Answers (1)

player0
player0

Reputation: 1

you will need 4 rules:

=FILTER(A2, COUNTIF(FILTER(F$2:F,G$2:G="Paladin"), A2))

=FILTER(B2, COUNTIF(FILTER(F$2:F,G$2:G="Paladin"), B2))

=FILTER(C2, COUNTIF(FILTER(F$2:F,G$2:G="Paladin"), C2))

=FILTER(D2, COUNTIF(FILTER(F$2:F,G$2:G="Paladin"), D2))

0

Upvotes: 1

Related Questions