random number question

[Greegan]

I will thank you in advance for your assistance

I have created bingo cards for a fund raiser. I had the cards created using
random numbers 1 through 75 (15 numbers for each letter B-I-N-G-O). And this
has work well so far.
I have been drawing the numbers out of a hat with cut up pieces of paper.
This is fine but I need something more... automated... So I'm coming to you
folk for help.

I want to set up an excel sheet that can be used over and over again.

I hope to have it like this..
B 1 2 3 4 5 ... 15
I 16 17 18 19 20 ... 30
N 31 32 33 34 35 ... 45
G 46 47 48 49 50 ... 60
O 61 62 63 64 65 ... 75

each letter and number appearing in their own cell

I want a random generator of 1 through 75
Each time a number is "rolled" I want it to light up on my sheet with a red
or green hightlight... maybe with the field changing a different color as
well.
But the problem is having the number picked with others already drawn.

I need a random number formula that relates to the ones selected already.

If I need to have the chozen numbers to be copied somewhere else and then
have the 'randomizer' relate to that 'list' of numbers then okay.

I'm hoping to have a button to reset the sheet and a button to "pick" the
next number...

Any help with this would be great.

Thanks

G

 

[Anne Troy]

The talented and lovely Chip Pearson already has a Bingo game available at
the bottom of this page:
http://www.cpearson.com/excel/games.htm
************
Anne Troy
www.OfficeArticles.com

 

[Greegan]

Hmmm that's really pretty cool. But I'm looking for something more than Mr
Pearson's Bingo game.

there has to be a simple way of doing this

 

[bplumhoff]

Hello,

To create random integers without repetition you can use my UDF
UniqRandInt().
See www.sulprobil.com

HTH,
Bernd

 

[Max]

Try this set-up, based on code by Tom Ogilvy in 2002 (slightly adapted)
Think it fits what you're after ..

Link to demo file: http://www.savefile.com/files/1978802
Random Draw without Replacement Program (Bingo).xls

Conditional formatting was set for the desired "rolled" results area
by selecting H13:V17 (with H13 active)
and using the formula: =COUNTIF($G$2:$N$11,H13)

The operation / play is simple and intuitive

As randbetween is used, ensure that the Analysis Toolpak
is installed and activated.
Check the "Analysis Toolpak" box (via Tools > Add-Ins)
Chip Pearson's page has details on the ATP at:
http://www.cpearson.com/excel/ATP.htm

 

[Max]

The operation / play is simple and intuitive

2 buttons are provided: Draw & Reset

Clicking "Draw" will randomly draw the numbers, one-at-a-time

The particular number drawn will be displayed prominently in S2

All numbers drawn will be consecutively listed in G2:G11,
then in H2:H11, I2:I11 and so on till N2:N6
in a columnar zig-zag fashion

The primary display panel for the drawn numbers,
i.e. the desired:

B 1 2 3 4 5 ... 15
I 16 17 18 19 20 ... 30
N 31 32 33 34 35 ... 45
G 46 47 48 49 50 ... 60
O 61 62 63 64 65 ... 75

sits within G13:V17.

Numbers drawn will "light up"* consecutively
on this panel as the draw proceeds
*triggered by the CF described earlier

Clicking "Reset" will clear the numbers drawn
and reset for a fresh draw

Try it ..