Escape the Sheet (Excel Puzzle)

Escape the sheet is a little Excel game I put together several years ago to teach a group of colleagues some more advanced Excel skills while having a little fun with Trivia and Logic puzzles, two things I love!

This game is a combination of excel formulas, conditional formatting for both the current cell and for values based on another cell and some VBA macros to make it a little more challenging to code up.

You don't really need any huge programming experience or expensive software to write a game, here's one you can do in excel.

The game is a small 2 parter, the first level is a trivia quiz.

There is a bank of 50 questions in the system though this could be more if you're so inclined.

To remove any possible ambiguity with spelling or case matching all of the questions have numeric answers.

The system will randomly present 5 of these questions to the player, the player may request a new set of questions.

Once the 5 questions have been answered, the system will then inform the player that they have not been successful and to try again or it opens up room 2.

Room 2 is a logic puzzle where a colour sequence this is also randomly generated and the player can regenerate at any time. Each of the colours is associated with a number 1-10, the player must use trial and error/memory to find the numbers that correspond and replicate the sequence.

Initially I set up the workbook with 4 blank worksheets.

Starting with sheet 3 I created a table with a header "Question" & "Answer"

In column A and using autofill numbers 1-50 are entered, it's important that the questions are numbered for the selection process.

I then go online and through my memory for 50 questions with numerical answers.

We now need a way to import these questions to the Room 1 screen on sheet 1.

This is done using a VLOOKUP function, this allows us to use the number at the beginning of the question row. However, if we just used 1-5 from the Room 1 sheet we would only ever get the first 5 questions and in the same order. This is where sheet 2 comes in, there are 2 rows of formulae here, the first uses the RANDBETWEEN function, this allows the user to enter a range between which a random entry will be generated. The problem is that with a small range such as this there is a high probability that there will be duplicates and that would not make for a very tricky quiz. So to overcome this there is a second column that takes this a step further and removes duplicates using both the RANK.EQ and COUNTIF functions, these rank the value against the rest of the list and count if there is a repeat of the value in the full list, these values are added together and this produces a random and unique value. We can confirm this by copying the list, then paste values and sort smallest to largest, this will show all unique values 1-50.

Back on Room 1, we can then use VLOOKUP to take the 1st, 2nd, 3rd etc. value from the list and look it up on the question sheet for both the question and the answer. Each time the random numbers are regenerated, a new set of questions will appear on the Room 1 screen.

Excel is configured to auto calculate, this causes a problem with the RANDBETWEEN function because every time a page is loaded, the calculation runs and reorders the questions. This can be set to manual buy going to Formulas in the Ribbon, then Calculation options and setting to manual, we will take care of calculation later.

Before we are done with this page we hide Column C (the actual answers)

The answer checking is really quite simple, The value of all the expected answers is summed into a cell at the bottom of Column C in Room 1, the players answers are loaded into Column D and summed.

A VBA script is programmed into a button to run the calculation and check the answers.

To add the button go to Developer in the Ribbon, add a button using Insert -> Button

Once you draw the button there is an option to add a new macro. In this case I called it check answers

All this does is runs the calculation (for this sheet only) and checks if the sum of the expected answers match the sum of the players answers. If the numbers don't match then a message box pops up to say try again, if they are successful then we unhide the sheet for Room 2.

There is also a conditional format placed in Cell B28 with an arrow, prompting the user to move tabs.

To get new questions for the Player we simply need to rerun the calculations on both sheet 2 (the random number sheet) and then on the Room 1 sheet, this causes the VLOOKUP to reference a new set of numbers and also to pull in the new associated questions. This piece of code also clears the Players answer section.

Room 2 is a logic and memory puzzle, the user us presented with a sequence of 6 randomly generated colours, again this uses the RANDBETWEEN function with the range of 1-10.

There are conditional formatting rules that based on the number in the cell that the fill and font colour change depending on the value.

The player must enter these numbers in sequence and then enter check, there is no clues to what colour is what number so as they test they must remember what result they got for each entry.

Again the values in the answer cells are summed, the Player answers are also summed and the macro runs the calc, fills in the colours and tests the result. This either prompts the player to try again or congratulates them on the win.

There is also a new sequence button to allow the player get new colours, this runs the calc but only on the cells in Row 10 and clears Player answers in Row 12.

I have tried to upload the game but as it is a .xlsm file this isn't allowed so feel free to download a copy from here, if you have any questions drop me a message.