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

1. ## 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

2. ## Re: Three questions starting with Index, Match giving #REF! error

Well I think I've answered the first two questions.

Not really sure about the third one. Partly because I'm not 100% sure I fully understand the requirement...

3. ## Re: Three questions starting with Index, Match giving #REF! error

Thanks for the quick response.

What I am looking to do with the third issue is, when all squares are filled out no further changes should be made to the horizontal or vertical numbers. They should be locked until a new game is started. As it is now, after all squares are filled out and the game progresses as you add the numbers for the quarterly scores the results also change and it alters the results. I am looking for a way to lock the random changes when all squares are filled out.

Jim O

4. ## Re: Three questions starting with Index, Match giving #REF! error

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.

5. ## Re: Three questions starting with Index, Match giving #REF! error

Thank you very much for your time and response. It has been most helpful.

I will mark this post as solved and repost in the VBA section about the necessary code.

Again Thanks much.

Jim O

6. ## Re: Three questions starting with Index, Match giving #REF! error

As a follow up here is the final solution using a Non Volitile Rand function.

Jim O

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1