Conditional Formatting based on Another Range

Question

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.

player0 · Accepted Answer · 2019-05-27 02:26:44Z

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))

answered May 27, 2019 at 2:26

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Parker Frank B Over a year ago

Thanks! This does work, the only concern I have is my master sheet I'm working on has 12 classes with 4 columns, so I'd need a total of 48 conditional formatting rules. Any possible way to combine all 4 of your examples into one?

player0 Over a year ago

nope. joining them under one formula will cause that only 1st column gets evaluated

Collectives™ on Stack Overflow

Conditional Formatting based on Another Range

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related