Bingo Card

[sinspawn56]

I am trying to make a bingo sheet using US States instead of numbers.

I have the basic 5x5 grid with the center cell being a free cell.

I need to populate the grid with random yet unique states (so that the states don't appear on the card more than once)

Any ideas?

I have attached my bingo template.

[shg]

If you can use VBA ...

Add this UDF to your workbook:

Public Function RandLong1(Optional bVolatile As Boolean = False) As Long()
    ' Returns an array of random numbers 1 to n to the calling range
    ' Adapted from J.E. McGimpsey  http://www.mcgimpsey.com/excel/randint.html

    Dim nNum        As Long     ' number of numbers
    Dim nRow        As Long     ' rows in calling range
    Dim nCol        As Long     ' columns in calling range

    Dim aiTmp()     As Long     ' initialized to sequential numbers
    Dim aiOut()     As Long     ' output array
    Dim iNum        As Long     ' index to aiTmp

    Dim iInx        As Long     ' decrementing ceiling for iRnd
    Dim iRnd        As Long     ' random number 1 to iInx
    Dim iRow        As Long     ' row index to aiOut
    Dim iCol        As Long     ' column index to aiOut

    If bVolatile Then Application.Volatile
    If Not TypeOf Application.Caller Is Range Then Exit Function

    With Application.Caller
        nRow = .Rows.Count
        nCol = .Columns.Count
        nNum = nRow * nCol
    End With

    ReDim aiTmp(1 To nNum)
    ReDim aiOut(1 To nRow, 1 To nCol)

    ' initialize aiTmp with sequential numbers
    For iNum = 1 To nNum
        aiTmp(iNum) = iNum
    Next iNum

    iInx = nNum
    For iRow = 1 To nRow
        For iCol = 1 To nCol
            iRnd = Int(Rnd() * iInx) + 1
            aiOut(iRow, iCol) = aiTmp(iRnd)
            aiTmp(iRnd) = aiTmp(iInx)
            iInx = iInx - 1
        Next iCol
    Next iRow

    RandLong1 = aiOut
End Function

The select A1:E5 and paste this formula into the formula bar:

=INDEX(A6:A52, RandLong1())

Don't press Enter; instead, press and hold the Cntl and Shift keys, then press Enter.

[sinspawn56]

The only issue I have with that is that it overwrites the Free cell which is C3. I do not want a state to appear here.

[shg]

Format that cell as ;;;"Free". Or put a picture over it.

[sinspawn56]

Most Excellent. Works perfectly. Thank you.

[mimiluni]

this was perfect! the VBA works like magic! thanks for this post!!
i simply placed an image for the FREE SPACE and the VBA works like a charm. i'm totally geeking out over this!

[Jakobshavn]

Here is a version without VBA.