Issue with conditional formatting cell range based on exact match text in one cell

1
votes

We track workshop registrations in a google sheet and I'm trying to conditionally format a range of cells (A7:P14) based on the text in cell E7 (Eng DLO, Eng TBC, Sp DLO, Sp TBC).

I used the formula

=COUNTIF($E7:$E, "Eng DLO")

and A7:S14 turned the selected color (light purple). When I then added conditional formatting to turn dark purple with the formula

=COUNTIF($E7:$E, "Eng TBC")

the color wouldn't change when I changed the value in E7 from Eng DLO to Eng TBC.

I know the issue is that I need it to EXACTLY MATCH the text and I tried incorporating EXACT into the COUNTIF formula, but it would only highlight E7 or just A7:P7 instead of the whole selected range in the conditional format (A7:P14).

Here is a sample sheet with what I am hoping it will eventually look like once I get the conditional formatting to actually work (I removed the conditional formatting). https://docs.google.com/spreadsheets/d/1Bn9FVTHE1OO49p4PKo6j0Qd3c0NX6pUq3vp0pHFNGVI/edit?usp=sharing

1
google-sheetstextconditional-formattingcountifexact-match

1 Answers

1
votes

Got a couple other issues here:

  1. The 7 is a floating reference, so you need to fix it with $. This is the reason your formula isn't working. In the next row, it would start counting in cell E8. With the $, it works, but less efficiently than it could, which brings me to point 2.
  2. If you're only referencing one cell, just check for equality against that one cell.

To the Eng TBC, you would use

=EXACT($E$7, "Eng TBC")

Just to demonstrate a point, without the dollar sign, the next rows cells would have been checking against this:

=EXACT($E8, "Eng TBC")

Which of course would have been empty.

A More Flexible Solution

Since you probably don't want to keep having to reformat per set of cells, you can use a ROW-based approach to tackle the issue. This one, for example, assumes 8 seats per group.

=EXACT("Eng DLO",INDIRECT("E"&(8 * INT((ROW() - 7) / 8) + 7)))

Every 8 rows, it references the next multiple of 7. (Yes, INT is FLOOR, but shorter.)