1

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

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Bri
  • 31
  • 5

1 Answers1

1

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

General Grievance
  • 4,555
  • 31
  • 31
  • 45
  • Thank you that helped with one of the issues. Now the issue I'm having is that I want to apply this conditional formatting throughout the sheet, but because of the absolute reference, when I pasted conditional formatting to the next 8 rows, it adjusted the color based on E7 instead of E15 (I added the conditional formatting with your formula to the test sheet so you can see what I mean). I'd rather not have to conditionally format each section of rows as that would be very time consuming. Is there an easy tweak so that the conditional formatting can be pasted/applied throughout? – Bri Sep 25 '20 at 14:28
  • @Bri I added a solution assuming 8 seats per set. Does that work for you, or do you need something better? – General Grievance Sep 25 '20 at 16:05
  • I plugged in your formula to the test sheet for the Eng DLO conditional formatting, but it changed the formatting in rows 15-22 and the formatting won't change based on the selection in cell E15, but still changes based on the selection in E7. – Bri Sep 28 '20 at 16:40