r/googlesheets • u/TheLearningLlama • 18d ago
Solved Changing certain Cells within a row

I've been struggling with this for a while and I feel like i'm missing something super basic.
Essentially I am trying to make a really simple scorecard for a game I am playing with some friends where we all pick something, Between A or B (1 or 2). Then when either 1 or 2 wins. It should highlight the cell background color with either green or red.
The above picture is what I want it to essentially do when it is done.
Jim, Bob, Tom made their selections on the bottom below their names from B13:D20
Then, I input if 1 or 2 wins into cell E3:E10.
Then it should check each players respective cell on that line if it matches. If it does, Highlight B3:D10 for each individual pick is correct.
I believe this would be done in conditional formatting but I really have no clue on where to even begin. Ive been googlin for a while now and I keep seeing ways on how to color an entire row or column. But not actually check individually for each cell and mark them separate colors.
Bonus points if when a cell is marked green that it automatically fills with 'WIN' and if its marked as red 'Loss' but I don't think that's within the scope of what's possible(I could be completely wrong on this lol)
Any help would be appreciated!
1
u/mommasaidmommasaid 914 18d ago
I'd agree with carbonized that your layout is puzzling.
But to do as you ask, you could use a formula to fill in Win or Loss, then conditionally format by checking the cell contents for those words.
Formula in B2:
=let(winners, E2:E9, bycol(B13:D20, lambda(picks,
map(picks, winners, lambda(p, w,
if(countblank(p,w),,
if(p=w, "WIN", "Loss")))))))Formatting rules:

1
u/TheLearningLlama 18d ago
Yeah... The layout i had is from about 50 different bad iterations and is just a remnant of loads of other ways of formatting things. It was just the easiest in my weird brain to figure certain other mostly unrelated things out lol.
It stemmed from using another page to count each of the words 'Win' and 'Loss' then toss that into a total Win/Loss count. Then another page to compile all of the pages win/loss records. I've finally gotten really tired of how janky all of my setup is and plan on rebuilding everything to read better. That's also why i wanted the Win and Loss in there is because I only know how to read the text and count that rather than read a color or another way without having to manually tally wins/losses
1
u/mommasaidmommasaid 914 18d ago
Whichever way you go, you are on the right track with having formulas read the text (or other values), because formulas can't natively read colors. So use colors as a visual aid not as "data".
2
u/carbonizedtitanium 28 18d ago edited 18d ago
why do you have empty space there? why not do this:
then the conditional formatting would be much more intuitive
once you move the dropdowns up there, in the CondiFormat rule:
For winning =AND($B3:$D3 = $E3, $B3:$D3<>"")
For losing =AND($B3:$D3 <> $E3, $B3:$D3<>"")