Forum Discussion
Conditional Formatting - Based on Blank Cells, Cell Contents, and More...
I have a worksheet where I list all our customer's orders for boat parts. There are tables for each boat size (on the Tables tab), and those are referenced to then make tables for each specific boat. I use a 12-color rotation for these tables (on the Boats tab). However, they cannot be formatted as tables specifically, because they are using the Unique and Filter formulas. The question: I am looking for a conditional formatting formula that will automatically color the cell right next to the first line of each of these "tables" so I know what color the next table should be in the rotation. I've attached an example spreadsheet to help with understanding.
So far, I've gotten it to MOSTLY work, but ONLY if each "table" only has 1 row of data (the color does not appear in the cell next to the first table in the list, and a repeat of the final color appears a second time below the final table):
But when we move to "tables" with more than one row of data...everything gets even messier and more incorrect:
If there's something that will just automatically format the "table" titles (the merged cells at the top of each), that's even better. But even just a small cell in the columns between as I have would be fine, as long as rotation is followed correctly.
Below are examples of each formula I'm currently using and what it's for:
Find unique boat numbers for each size: =SORT(UNIQUE(_33[B]))
Each "table title": =Tables!W$3&"-"&Tables!W4
Each "table" data: =UNIQUE(FILTER(_33[[CPN]:[M]],(_33[B]=Tables!W4)*((COUNTIF(Tables!$D$4:$D4,Tables!$D4)=1)+(COUNTIF(Tables!$F$4:$F4,Tables!$F4)=1)+(COUNTIF(Tables!$G$4:$G4,Tables!$G4)=1))))
Conditional formatting for "in-between columns": =FILTER(B2:B4000,MOD(COUNTA(UNIQUE($B$4:$B4))-1,12)=11)
3 Replies
I don't even begin to understand your conditional formatting formulas.
For A4:A4000, use
=MOD(COUNTA($B$4:$B4),36)=1
=MOD(COUNTA($B$4:$B4),36)=4
...
=MOD(COUNTA($B$4:$B4),36)=34
The 36 is because each "table" has 3 rows: 3*12=36.
The number at the end of the rules increases by 3.
For F4:F4000 use:
=MOD(COUNTA($G$4:$G4),84)=1
=MOD(COUNTA($G$4:$G4),84)=8
...
=MOD(COUNTA($G$4:$G4),84)=78
The 84 is because each "table" has 7 rows: 7*12=84.
The number at the end of the rules increases by 7.
And similar for larger "tables".
- Is there some way to make this work in the case that the "tables" are not all one consistent size?
I fear you'd have to set up separate rules for the "tables".
