UK LOTTERY CHECKER EXCEL 97 & 5-95 SPREADSHEET VERSION 1.00 1. Quick start. 2. Introduction. 3. Disclaimer. 4. Instructions. 5. Checking technique. 6. Generating more tickets. 7. Last note 1. Quick start Enter ticket numbers in the boxes provided. At draw time enter the draw numbers in the top line. Then check the column to the right of the ticket lines for your results. For more tickets copy the whole of the last box to underneath itself, leaving one row separating them both. 2. Introduction. I first designed this spreadsheet for the UK LOTTERY on a Psion Series 5 after having sold my Series 3c. I had been using a shareware program on the 3c to check my tickets. I was sure the 5's spreadsheet application was more than capable of basic number checking until a dedicated shareware program came out for the Series 5. Eventually I also implemented error checking of inputted ticket & draw numbers, namely missing numbers, duplication & numbers outside the 1 to 49 range. I then created this PC version in Excel 97, although the file is saved in the dual Excel 97 & 5-95 format. If using Excel 5-95 winning lines will be shown in upper case in the results column, lines with less than 3 matches and any error messages will be shown in lower case. If using Excel 97 the same case sensitive formatting of results is shown, but this is expanded with conditional formatting. This will highlight a cell with red, yellow or orange for certain errors and more importantly winning lines with green. 3. Disclaimer I must stress that I use this spreadsheet as an aid to checking both losing & winning ticket lines. I will explain this technique in later paragraphs. You use this spreadsheet at your own risk & I accept no responsibility for the following. Anyone missing any winning numbers by using this spreadsheet, anyone inputting their own ticket numbers incorrectly & anyone inputting the draw numbers incorrectly. 4. Instructions. Once in the file you will see that the spreadsheet has 2 input areas. The row under the top row is where the draw numbers & bonus ball are entered. Below this are four tickets of 7 lines each for you to input your ticket numbers, although more can be generated if required. To the right of the tickets is the results column which displays how many ticket numbers match the draw numbers and any input errors. Simple demonstration draw & ticket numbers are already input to allow you to see the permutations of results possible. You can input your ticket numbers over the demonstration numbers. For any of the demonstration ticket number lines you do not overwrite with your own numbers, you can remove these by highlighting them & pressing the DELETE key. This will obviously delete the contents, but does not delete the box border formatting. PLEASE ALSO ENSURE YOU INPUT YOUR TICKET NUMBERS CORRECTLY. Then it is advisable to highlight your ticket numbers & lock the cells from the cell formatting menu option. Then ensure the sheet is protected from the tools menu options. This will prevent deletion or accidentally inputting numbers over the top of the original ones. The draw number input cells are obviously not locked and you will still be able to input them. The spreadsheet also checks the draw & tickets numbers for the following input errors. Duplication (Ticket number cells highlighted in yellow in Excel 97), missing numbers & numbers outside the 1 to 49 range (These cells are highlighted in orange in Excel 97). Ticket number input errors will also have an error message displayed in the result column opposite the ticket line in question (Highlighted in red in Excel 97). Draw number cell errors will be displayed in the same fashion as above and the draw number error message will be displayed on the line immediately below the draw numbers. Also if a draw number error occurs an error message will be displayed in every result box for each ticket line. This will overwrite any ticket number errors, if any & the normal result message until the draw number error is corrected. Spurious error messages can occur if recalculation is set to auto until all of the draw numbers are input. E.G. duplication of the last Lottery draw numbers with the current ones. 5. Checking technique. As explained earlier I do not totally rely on the spreadsheet to show the winning numbers, as I use it as a quick aid to checking all lines. To this end the panes are frozen one line below the draw numbers (PLEASE ENSURE IT STAYS THIS WAY AS THE ERROR MESSAGES FOR THE DRAW NUMBERS ARE DISPLAYED IN THIS LINE). This helps as you page down so you can cross check your ticket line numbers against the results column & the draw numbers at the top. 6. Generating more tickets The spreadsheet comes with four tickets already supplied. The minimum number of tickets required for it to function correctly is two. If you do not use all the ticket lines do not worry as the results column is designed to only display for the tickets lines entered. If however you require more than four tickets please carry out the following instructions exactly. Place the cursor in the top left hand cell of last ticket box on the spreadsheet & then holding down the shift key highlight over to the results column & then down to Line G. Select copy "Ctrl C" & then paste "Ctrl V" these to immediately underneath this last ticket just copied, leaving one row separating them. 7. Last note I designed the spreadsheet to be simple, no random number generator or storing of numbers, that sort of thing, just a plain old number checker. Its just a case of entering your ticket numbers, then the Draw numbers at Draw time & then recalculating. I hope you will find this eases the burden of your weekly chore, if you have a lot of tickets or are part of a large syndicate. Thank you Derek Joinson. dek@dmjoinson.freeserve.co.uk