+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    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
    Attached Files Attached Files
    Last edited by JO505; 11-02-2013 at 05:42 PM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,914

    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...
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

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

    BadlySpelled,

    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. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,914

    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.
    Last edited by BadlySpelledBuoy; 11-02-2013 at 05:44 PM.

  5. #5
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

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

    BadlySpelled,

    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. #6
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Simple Index Match formula giving me problems with 2 way lookup
    By rikkyshh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-18-2013, 06:47 AM
  2. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  3. Index/ Match/ Lookup questions
    By sujithr25@yahoo.com in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2012, 09:57 PM
  4. [SOLVED] Using Index Match or Vlookup giving unexpected results on imported data
    By jacob@thepenpoint in forum Excel General
    Replies: 2
    Last Post: 07-03-2012, 05:49 PM
  5. worksheetfunction.match giving run time error '1004'
    By devo2511 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-15-2010, 04:47 PM

Bookmarks

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