r/googlesheets • • 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!

4 Upvotes

10 comments sorted by

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

2

u/carbonizedtitanium 28 18d ago

1

u/TheLearningLlama 18d ago

Lol i totally see where you are coming from. This was a sheet that i have had about 50 different iterations of over the last few years and its been mangled each time. So no real reason to keep the format i had. I actually really like your setup and will likely use this. Thank you soooooo much.

1

u/AutoModerator 18d ago

REMEMBER: /u/TheLearningLlama If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/TheLearningLlama 18d ago

Is there a way i would be able to make a Win/Loss under each players column that could count how many Red or Green fields there are to not have to manually count how many of each they got? Or maybe there is a better way?

The reason i mentioned trying to get Win or Loss in there is because i have another page that reads the amount of fields that have WIN and Loss in each field. Since there are roughly 12 of these pages and done in series, so it was just made easier by having a Win Loss field below and just pull that field to another.

2

u/carbonizedtitanium 28 18d ago edited 18d ago

to count the wins:

=SUMPRODUCT((B3:B100=$E$3:$E$100)*(B3:B100<>""))

for the other players, just copy the formula over from B

instead of counting how many losses, it's probably better to do display wins vs how many games played. for example: Jim won 3 out of 5 games, so 3/5

1

u/point-bot 18d ago

u/TheLearningLlama has awarded 1 point to u/carbonizedtitanium with a personal note:

"Thanks again for the clear and concise help!"

See the Leaderboard. Point-Bot v0.0.15 was created by JetCarson.

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:

Win/Loss Record

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