Isolating conditional formatting to each range in a column

Question

I've set up conditional formatting to highlight the top 3 values (gold, silver, bronze) in a range of rows. However, I want to be able to copy/paste the conditional formatting below the initial rows for multiple datasets and have the conditional formatting function on each individually.

Currently, I have it working in the initial dataset, but it breaks when copying more below it. My conditional formatting is currently (with two additional formats for silver and bronze)

=C3=LARGE(C$3:C$8,1)

I've tried pretty much every variation in absolutes and relatives, as well as using RANK without success.

Here's a link to it as well https://docs.google.com/spreadsheets/d/1kIYfF2cCDfWTYVebFsXxveh2f9GoOjydAG2XOiFw2v0/edit?usp=sharing

Answer 1

Russell, I've gone in and made a new sheet ('Erik CF') wherein I've implemented the conditional formatting. Here is a brief explanation:

1.) I removed unused columns and rows. You should always do this, since it speeds up processing, especially for full-column array formulas (which are used in my conditional formatting setup).

2.) The four CF formulas are applied to the entire scored range (i.e., C3:E). This is what makes the formulas so complex: you don't need to apply them separately to different ranges, but they must also locate each block of scores.

3.) To achieve this, I've included many INDIRECT calls within the formulas. INDIRECT allows us construct a string that we can then use as a live range. Thing is, those live ranges are unknown. So I assessed that the markers of where a live score range begins and ends is a number preceded by a null cell and a number followed by a null cell, respectively. So the CF formulas create forward- and backward-looking QUERYs and then use VLOOKUP to find those nulls for any given score (i.e., the closest null before it and after it). Those positions are then used to Frankenstein together the INDIRECT ranges for every score.

4.) The three main formulas (i.e., those that apply gold, silver, bronze) are essentially the same "custom formula." The only difference is the very last number in the very long formula, which assigns the LARGE value of 1, 2 or 3.

That said, for future reference here on StackExchange, here is the formula for applying first place/gold to the scores in range C3:E (where, again, changing the last number to 2 or 3 would find second place and third place):

=C3=ArrayFormula(LARGE(INDIRECT(LEFT(ADDRESS(ROW(C3),COLUMN(C3),4),1)&VLOOKUP("/",QUERY({INDIRECT(LEFT(ADDRESS(ROW(C3),COLUMN(C3),4),1)&"2:"&ADDRESS(ROW(C3),COLUMN(C3),4))&"/",ROW(INDIRECT(LEFT(ADDRESS(ROW(C3),COLUMN(C3),4),1)&"2:"&ADDRESS(ROW(C3),COLUMN(C3),4)))},"Select * Order By Col2 Desc"),2,FALSE)&":"&LEFT(ADDRESS(ROW(C3),COLUMN(C3),4),1)&VLOOKUP("/",QUERY({INDIRECT(ADDRESS(ROW(C3),COLUMN(C3),4)&":"&LEFT(ADDRESS(ROW(C3),COLUMN(C3),4),1))&"/",ROW(INDIRECT(ADDRESS(ROW(C3),COLUMN(C3),4)&":"&LEFT(ADDRESS(ROW(C3),COLUMN(C3),4),1)))},"Select *"),2,FALSE)),1))

As complex as that formula is, it is easy to modify, in that all you'd need to do to apply it to any range is change every "C3" to the upper-leftmost cell in the range to which you're applying it.