Excel 2007 : Lotto check sheet

[jaydoyle1977]

Hi,
Hope this is the right forum to post this, if not, please redirect me.

Im looking for help designing a check sheet for a home based lotto bingo game.
The game consists of each player having a 6 number lotto card. With each lotto draw, 6 numbers will be marked off.
I want to design a sheet that will highlight each matching number from each players card without the need for me to manually check each card after each draw.
Any ideas?

[Paul]

Hi Jay, and welcome to the forum.

I'm in a lottery "bingo" game as well, and designed a spreadsheet with some functions and a custom function to be able to track when users numbers are drawn in the real lottery. In mine, though, each player picks 10 numbers, and the six lottery numbers drawn are then compared to those picks and the matches are highlighted.

I'll post a copy when I get home tonight and you can take a look at it to see if it meets your needs or might require some adjusting.

[Paul]

Hi Jay,

I adjusted my workbook to only allow the users to pick 6 numbers, and there's spaces to enter each of the 6 drawn numbers. There is some code in the Sheet1 module (to trigger calculation when you change one of the draw number cells), in a standard module used in column H (=countcells function), and a couple of conditional formatting rules to highlight numbers that match the drawn numbers, as well as the maximum value in column H.

The small table to the right also shows the max # of matches by any one person, and how many people have matched 0 through 6 numbers in all.

Hopefully this gives you a good idea, and maybe it will work for you.

[mattdowdle]

Hey Paul,
I am actually in a lottery bingo and found your posting very helpful.

My lottery bingo sounds very similar to the one you mention (10 numbers, 6 lottery numbers, etc.) and I was hoping you could post your original spreadsheet for me to use. I downloaded your amended version and tried to manipulate it back to 10 numbers, however I keep getting an error message "circular reference" when I add the 8th number and beyond.

Anyway, if you could post your 10 number version it would be much appreciated.

Thanks,
MD

[Paul]

Here's the 10-number version for anyone who is looking for it..

[huberto]

hello all,
Im new here, but can you help me with the lotto check sheet ef_lotto xslm.
That's working with 6 lotto numbers, but we use for the bingo also the seventh number.
Anyway, if you could help me, it would be much appreciated.
Thanks
Hubert

[Paul]

Hi Huberto,

To make the sheet work for 7 drawn numbers instead of 6, make the following changes:

1. Open the VB Editor (press ALT+F11). In Module1 change the range "N2:S150" to "N2:T150"

2. Also in the VB Editor, double-click the "Sheet1 (Sheet1)" and make the same change.

3. On the worksheet itself, select the range B2:K162 then open the Conditional Formatting dialog. Change the formatting rule formula for that range from

=COUNTIF($N$2:$S$151,B2)>0

to

=COUNTIF($N$2:$T$151,B2)>0

That should do it. Add the number 7 in cell T1 if you want, just so you know that's the column where you'll enter the 7th number drawn.

Hope that helps!

(PS - Please take a moment to read the forum rules, and in the future start your own thread and include a link back to this one.)

[huberto]

Thanks a lot

Hubert

[bufftom]

Thank you been looking for this for 2 months very helpful thankyou,

[sdhinternet]

Thank you been looking for this for 2 months very helpful thankyou,

Can this be edited to be used for 9 numbers? if so how would I do it?

[Paul]

Yes it can. What have you tried?

If you look at both versions (conditional formatting, formulas, vba code) you'll see what changed - and that will also tell you what needs to change to accommodate different amounts.

[Jock]

Hi Paul

Thanks for the spreadsheet. Saved me a lot of time. Changed one wee thing, the colour of the winning "matches" to green Oh and i also added a macro to sort it in winning matches to the top.

Again, thanks for your efforts

Gary

[garyshogg]

Hi Paul

Great spreadsheet, thank you. Just wondering how to change the colour of the numbers marked off from yellow to a darker colour, so it prints darker on my black & white printer.
Cheers
Gary

[Paul]

Hi Gary,

Thanks for the feedback.

To change the bright yellow to some other color, select cells B2:K162 (or the range of cells you're using to show users' picked numbers) and then click Conditional Formatting > Manage Rules > select the COUNTIF formula that applies to that range and click Edit Rule... > click the Format... button > on the Fill tab select another color and click OK a few times to return to the worksheet.

That should do it!

[blaydonian]

Hi paul

what if someone does not pay on a certain week is there any way of undoing the fill on a certain number or numbers just for that week

[Paul]

Yes, delete their row for not paying. The way it's setup is not meant for someone to be eligible only on certain dates. You pay your entry fee and you're entered until someone matches all 10 (or 6 or 9, etc) numbers.

[orangeanorak]

I note this thread is quite old however if anyone is still interested see my bingo lottery checker on www.thelotterychecker.com
Hopefully this will help someone