Excel crossword puzzle that triggers conditional formatting when won?

November 7, 2019 2:47 PM

I’m making a custom crossword for which the player will input their guesses into Excel. My plan is to have two sheets in this document: a hidden puzzle with all the correct letters in it (the player is on the honor system), and a visible sheet with the proper boxes blacked-out and the rest empty. (Obviously each cell has to correspond exactly between them.) I know just enough about Excel to know that there’s gotta be a way to set up a visible indicator when the puzzle is completed successfully, but…

…if I set up a simple “equal to its counterpart cell on the hidden sheet” conditional formatting rule on one cell, it lights up as soon as a single correct letter is input. How can I “postpone” the big reveal until all the cells on the puzzle sheet match the answer sheet?

posted by CheesesOfBrazil to Computers & Internet (5 answers total)

If you have one cell that is true only when all cells are equal (use and for all the matching letters), you can just use conditional formatting to change the entire puzzle when that cell is true.
posted by jeather at 3:18 PM on November 7, 2019 [1 favorite]

Could you do something like COUNTing up all of the TRUE matches and trigger the reveal when that count equals the total number of white squares in the puzzle?
posted by bcwinters at 3:18 PM on November 7, 2019 [1 favorite]

Best answer: The quick hack way I would do it would be to have a third sheet, and each cell could have for example (in pseudo code)

if A1 on sheet 1 is the same as A1 on sheet 2, value =0, otherwise value = 1

(i.e. =IF(Sheet1!A1=Sheet2!A1,0,1) , drag right and then down to the size of your crossword)

Then in the cell with the "reveal", do a sum check for the whole range, and if the sum = 0, then all cells match (i.e. =IF(SUM(A1:I9)=0,"Done","Keep Going").

You can then use conditional formatting to check if the reveal cell = Done, if so highlight green or whatever.

You COULD do it in one crazy run of nested IF statements but it would be unwieldy FAST.

I'm sure there are nicer ways to do this.
posted by trialex at 3:55 PM on November 7, 2019 [1 favorite]

Response by poster: Quick is good! And it works! Thanks trialex.
posted by CheesesOfBrazil at 4:08 PM on November 7, 2019

There are some other protections you could use in Excel that might make your solution more robust.

You can password lock your spreadsheet so that people can't unhide the hidden sheets or enter data into cells they shouldn't rather than leaving it strictly on the honour system. It's not unbreakable, but it'll keep people from easily messing with it.

The main thing you want to do is Hide the Sheets you are using to do the calculations - right click on the tab and choose 'Hide Sheet' and then click Protect Workbook and enter a password before sending it to people. If they never go looking for hidden sheets, they'll never know the password is there.

You might also want to lock the black cells so they can't accidentally enter data there. You can choose to protect/unprotect cells using right click -> Format Cells and choosing/not choosing the option under Protection. Then when you choose 'Protect Sheet' on the review menu, the selected cells won't allow data to be entered.

You might also want to limit what values people can enter into a cell using Data Validation. To one of the 26 capital letters, perhaps, or to strings of length 1, in order to keep them from putting bad data into the sheet. If you don't enforce this kind of control, you should try to be fairly robust in how you check for matches to allow for capital or small letters.
posted by jacquilynne at 9:12 AM on November 8, 2019