Yes, this can be done with four custom CF rules, each applied to range A1:X24 (the range of the "chess board":
Rule 1 (white):
=AND(ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)),ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3)))
Rule 2 (black):
=AND(ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)),ISODD(ROUNDDOWN((COLUMN(A1)-1)/3)))
Rule 3 (purple):
=AND(ISODD(ROUNDDOWN((ROW(A1)-1)/3)),ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3)))
Rule 4 (yellow):
=AND(ISODD(ROUNDDOWN((ROW(A1)-1)/3)),ISODD(ROUNDDOWN((COLUMN(A1)-1)/3)))
The order of the rules in the list is unimportant, since there is no overlap in conditions.
The Logic:
If the chess board grid were 8 x 8 single cells, the four divisions would be as follows:
Odd Row Odd Column
Odd Row Even Column
Even Row Odd Column
Even Row Even Column
Since you've got three rows and columns, we just get back to basics by using ROUNDDOWN( /3)
. In other words Rows 1, 2 and 3 all reduce to being 1; likewise, Columns 1, 2 and 3 all reduce to being 1. So any of those would reduce to 1 x 1, etc.
Since I used ROUNDDOWN
, the first block actually equates to 0 x 0 (i.e., EVEN x EVEN), and so forth from there. I could have used ROUNDUP
, which would have caused the first block to be 1 x 1, hence ODD x ODD. It's really irrelevant. I just chose to start from a zero count because it makes most sense in terms of computer arrays.
Update: (after further clarification of the goal)
What you want done is quite a bit more complex. Typically, I would say it goes beyond the type of help that can be obtained via a free forum like this, due to the custom, time-intensive, expert-required nature of working through such logic and setting up such formulas. In this rare instance, I'm happy to break my own "rule" and provide the formulas. See the newly added sheet "Erik Help 2" and the four new custom CF rules there.
Here are the rules:
white (unchecked):
=AND( OR( AND( ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)), ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3))), AND( ISODD(ROUNDDOWN((ROW(A1)-1)/3)), ISODD(ROUNDDOWN((COLUMN(A1)-1)/3)))), INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=FALSE)
white (checked, i.e., yellow):
=AND( OR( AND( ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)), ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3))), AND( ISODD(ROUNDDOWN((ROW(A1)-1)/3)), ISODD(ROUNDDOWN((COLUMN(A1)-1)/3)))), INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=TRUE)
black (unchecked):
=AND( OR( AND( ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)), ISODD(ROUNDDOWN((COLUMN(A1)-1)/3))), AND( ISODD(ROUNDDOWN((ROW(A1)-1)/3)), ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3)))), INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=FALSE)
black (checked, i.e., purple):
=AND( OR( AND( ISEVEN(ROUNDDOWN((ROW(A1)-1)/3)), ISODD(ROUNDDOWN((COLUMN(A1)-1)/3))), AND( ISODD(ROUNDDOWN((ROW(A1)-1)/3)), ISEVEN(ROUNDDOWN((COLUMN(A1)-1)/3)))), INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=TRUE)
I will not explain the full workings of these formulas here. However, I will point out that they follow similar logic to the previous four formulas.
Directing your eye, this part is the same in all formulas:
INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=
That is the part that "finds" where the checkbox is in each 3x3 block. And after the final =
, you'll see either TRUE
or FALSE
(i.e., checked or unchecked)
The top part of each of the four formulas simply combines each combination of possibilities from the previous four formulas (i.e., the white rule with the yellow rule; and the black rule with the purple rule), paired with either a FALSE
or TRUE
for checked or unchecked.
Here is a summary of the logical pieces in more readable fashion than the order they appear in the formula, for the sake of making it easier to understand:
ISEVEN
(AND
) ISEVEN
... (OR
) ... ISODD
(AND
) ISODD
:
(checked? =) FALSE
ISEVEN
(AND
) ISEVEN
... (OR
) ... ISODD
(AND
) ISODD
:
(checked? =) TRUE
ISEVEN
(AND
) ISODD
... (OR
) ... ISODD
(AND
) ISEVEN
:
(checked? =) FALSE
ISEVEN
(AND
) ISODD
... (OR
) ... ISODD
(AND
) ISEVEN
:
(checked? =) TRUE
Round 3
I'll take one more swing at this, though it does seem that the goal is actually getting more muddy and amorphous rather than becoming more focused. And this no longer represents anything like a "chessboard" (which has a set/predictable repeating pattern) other than that a chessboard and your sheet both fall under a broad umbrella as "grids."
See the newly added "Erik Help 3" sheet.
You now have three colors introduced as options: WHITE, BLACK, GREEN. And each of those will have a checked or unchecked state. So the minimum number of rules is 3 colors x 2 states = 6 rules. (If you will have 8 colors, your minimum number of rules will be 16, etc.)
The rule is essentially the same for every state (here, I will show it for WHITE/unchecked):
=AND( INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)))=FALSE, INDIRECT(ADDRESS(MROUND(ROW(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3), MROUND(COLUMN(A1)+IF(AND( MOD(ROW(A1)-2,3)=0, MOD(COLUMN(A1)-2,3)=0),0,1),3)-1))="WHITE")
The only parts that will change are the final color word in quotes, and the FALSE
midway through (which will be either FALSE
for unchecked or TRUE
for checked).
As such, you can copy that rule somewhere, make those two changes as you go for each color/state, and then copy and paste it into the rule field.
If this somehow all works as you had hoped, fantastic.
But if I've still got it wrong, I'm afraid I really do need to make the choice to bow out at this point and let someone else see if they can understand what you want better than I apparently am able to do.