UK Bingo cards

craigey1

New Member
Joined
Apr 6, 2020
Messages
30
Office Version
  1. 2019
Platform
  1. Windows
Apologies for asking for so much help on my very first post here.

I've been searching all over for a solution to be able to produce a UK style bingo card within Excel using formulas or VBA, but so far have only found code that's capable of producing singular games rather than a set of 6. I've searched here & seen that the question has been asked before, but so far no-one seems to have been able to come up with a solution. I thought I could look at randbetween() to generate the necessary numbers for each column, but couldn't see how to then comply the required game layout rules. I really don't know where to start with this. I'd normally try & then ask for help when stuck, so here I am!

The UK bingo games use numbers 1 to 90 (including 90) with the numbers split across 6 games of 3 rows by 9 columns. The number 1 to 9 would appear in the first column, 10 to 19 in column 2, 20 to 29 column 3 & so on until the 9th column which also includes the number 90. All numbers would only appear once across all 6 of the games & each box has 5 blanks across each row & can have 0, 1 or 2 blanks per column.
In summary:
1. A bingo 'card' is a set of 6 individual 3 x 9 grids, stacked vertically, which contain the numbers 1-90
2. The numbers 1 - 9 (9 numbers) are in the first column and 80-90 (11 numbers) in the 9th, the intervening columns (2-8) contain 10 numbers each.
3. An individual box has 15 numbers, 5 per row and between 0 and 2 per column

I'd appreciate any help with this as was hoping to generate cards for friends / family during the lockdown.

thanks in advance

jumbobingoticket-jpg.10662
 

Attachments

  • JumboBingoTicket.jpg
    JumboBingoTicket.jpg
    119 KB · Views: 3,453
Sorry about this .
I'm having trouble with this, its not working with my system, the error message that pops up is:
(Excel cannot open the file Bingo.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.)

If you can help me out with this please.
Thanks
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
I don't know which file you are using.
Have you tried this one (most recent one that was posted by lrobbo314):
Bingo
 
Upvote 0
I don't know which file you are using.
Have you tried this one (most recent one that was posted by lrobbo314):
Bingo
Hi,
I havent use this one, because some people dont understand how to play. So were you have game 1/2/3/4/5/6, they should be Panel 1/2/3/4/5/6 but there should be a space between each panel like the picture.
Is there a way to fix that bud.
Thanks for getting back to me..
 
Upvote 0
I'm not sure what picture you're referring to. I'm guessing you mean the one of the green Bingo ticket.
I've kind of recreated that by saving a copy of the workbook that I linked before & creating a new sheet. In the new sheet I referenced the Generated Bingo numbers from the Bingo Tab:
So C2 has the formula
=IF(ISBLANK(Bingo!B2),"",Bingo!B2)
I copied this formula across to column K2 & repeated for C3 to K3 & C4 to K4
in C6 the formula referenced the first line of the 2nd game =IF(ISBLANK(Bingo!B5),"",Bingo!B5) again I copied this to Column K

Eventually you end up with something that looks like a bingo ticket - after a bit of formatting.


Cell Formulas
RangeFormula
C2:K4C2=IF(ISBLANK(Bingo!B2),"",Bingo!B2)
C6:K8C6=IF(ISBLANK(Bingo!B5),"",Bingo!B5)
C10:K12C10=IF(ISBLANK(Bingo!B8),"",Bingo!B8)
C14:K16C14=IF(ISBLANK(Bingo!B11),"",Bingo!B11)
C18:K20C18=IF(ISBLANK(Bingo!B14),"",Bingo!B14)
C22:K24C22=IF(ISBLANK(Bingo!B17),"",Bingo!B17)
 
Upvote 0
I'm not sure what picture you're referring to. I'm guessing you mean the one of the green Bingo ticket.
I've kind of recreated that by saving a copy of the workbook that I linked before & creating a new sheet. In the new sheet I referenced the Generated Bingo numbers from the Bingo Tab:
So C2 has the formula
=IF(ISBLANK(Bingo!B2),"",Bingo!B2)
I copied this formula across to column K2 & repeated for C3 to K3 & C4 to K4
in C6 the formula referenced the first line of the 2nd game =IF(ISBLANK(Bingo!B5),"",Bingo!B5) again I copied this to Column K

Eventually you end up with something that looks like a bingo ticket - after a bit of formatting.


Cell Formulas
RangeFormula
C2:K4C2=IF(ISBLANK(Bingo!B2),"",Bingo!B2)
C6:K8C6=IF(ISBLANK(Bingo!B5),"",Bingo!B5)
C10:K12C10=IF(ISBLANK(Bingo!B8),"",Bingo!B8)
C14:K16C14=IF(ISBLANK(Bingo!B11),"",Bingo!B11)
C18:K20C18=IF(ISBLANK(Bingo!B14),"",Bingo!B14)
C22:K24C22=IF(ISBLANK(Bingo!B17),"",Bingo!B17)
 
Upvote 0
Sorry again, im not sure what is going on. Is there a way that you can send me the excel file that you are using please.
Thanks for the help.
 
Upvote 0
Use the file that I mentioned in post 62
Create a new sheet and and.paste the formulas that are in 64.

I believe sending files directly goes against the forum rules.
 
Upvote 0
Use the file that I mentioned in post 62
Create a new sheet and and.paste the formulas that are in 64.

I believe sending files directly goes against the forum rules.
Sorry again,
I must be doing something wrong.
I've put in the formulas into the cells & all im getting is #REF!.
 
Upvote 0
Sorry again,
I must be doing something wrong.
I've put in the formulas into the cells & all im getting is #REF!.
Please explain what are you doing? I.e what you are putting in to each cell and on which sheet
 
Upvote 0
I believe sending files directly goes against the forum rules.
It is, but if you need to share the actual file rather than posting an XL2BB example you can upload the file to a free file hosting site like www.box.com or www.dropbox.com, mark it for sharing and paste the link it provides in the thread.

Make sure that you sanitize any sensitive data before uploading the file (I doubt that is an issue in this case)
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top