Google Sheets: How Do I Colour A Cell In 2 Cases Context Based on Values From Another Sheet

Question

I'm trying to create a heads up display on the number of Hints a user has collected in a contest. There are different categories of hints here.

So I'm trying to create a process such that in the respective Hints tab (ie Bonus Hint in this case)

Filling in Column B (with the hint description), renders that Hint as being "obtained" and hence showing up "Green" in my Hints Overview sheet.

However, for "unobtained" hints, by not filling in Column B, I want my Hints Overview to instead still show the Hint number but in light gray (as illustrated below)

I dont want the unobtained hints to be empty cells. I want to make them looked grayed out. ^^^

I tried pulling some conditional formatting tricks like indirect(), ISBLANK() but something about CF just doesn't allow me to get what I desire.

And the other challenge is that because I arranged my headsup-display to be grid-like, the cell filling has to take on horizontal filling. Its not like as I can simply drag and fill 250 rows vertically.

How can I pull these things off in Google Sheets? (trust you guys to please dont maliciously edit my sheet out of mischief)

https://docs.google.com/spreadsheets/d/1IG6l0xkJSU-mxP6YVr73RwinEcdxSePI4HN02KF-WoA/edit?usp=sharing

Answer 1

delete everything in DASHBOARD!D8:AB17

use this in D8:

=SEQUENCE(10, 25)

remove your green CF rule and set the font to gray

now add this new CF rule as custom formula:

=INDEX(VLOOKUP(D8, {ROW(INDIRECT("BONUS!B2:B"))-1, INDIRECT("BONUS!B2:B")}, 2, 0))<>""

and set background to your green and font to white

---

also change DASHBOARD!AD17 to:

=COUNTA(BONUS!B2:B251)