Three questions starting with Index, Match giving #REF! error

I am trying to put together a sheet that will automatically assign random numbers to a 10 X 10 grid of names and return selected data.

It is a sheet to pick winners from a football pool by quarter ending scores. A player selects a square in the grid and at the end of each quarter the last digit for each team is matched to the numbers in Row D14:M14 and Column C15:C24. I have a formula that works some of the time but not all of the time and I am at a loss as to why it is selective in what it returns (see R15:R18).

Two other items I would like to address is:

A) Is it possible to have the RAND numbers show up as 0 thru 9 rather than 1 thru 10?

B) Is it possible to shut off the RAND function when the last square is filled (100 entries) and re-activate when the sheet is RESET?

Thanks,

Jim O

Jim,

A little beyond me that one I'm afraid. Not sure it would be possible with formulas alone, but there are far smarter people on here than I, so maybe someone will show you a solution.

I have a feeling it's going to require some nifty VBA, and that's well out of my league!


On a side note, if you change your formula in N13 to =IF(COUNTA(D15:M24)=100,"ALL SOLD",SUM(100-COUNTA(D15:M24))&" To Sell") and use the amended formulas I already put in cells R15:R18, you do not need the data below the 10x10 grid at all (unless it feeds into something else that isn't included in your example workbook).

Sorry I can't help further...

BSB.