Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Random Num - Lookup query (Excel 2003)

    Edited by HansV to present data in table.

    I run a school fundraising club, and want to keep the info in Excel.

    Each member has a unique IDNo, starting at 1 and running to approx 250. I then hold a note of the number of tickets they hold in a monthly random draw. This can vary from 1 to 10.

    Each month, I want to use a random number function to select the winner. The number of tickets will vary each month as members join & leave, and I have a way to calculate the current valid membership in any month. Let

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Random Num - Lookup query (Excel 2003)

    See the attached workbook. I added a cumulative total of valid tickets in column E.
    H1 contains a formula for the random number (it will be updated each time the worksheet is recalculated, for example by pressing F9)
    H2 contains a formula that looks up the name corresponding to the random number. It is an array formula, confirmed with Ctrl+Shift+Enter instead of Enter.

    =INDEX(B2:B1000,MATCH(1,(E2:E1000>=H1)*ISNUMBER(E2 :E1000),0))

    You can expand the range if necessary, but since you say your IDNos run to 250, this should be ample.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Random Num - Lookup query (Excel 2003)

    Thanks

    I've spent ages looking at thisin Excel. I originally wrote in Access, and I've never done anything like this in Excel.

    Thanks a million.

    Colin

    PS I may be back!!! with my next stoopid query

Posting Permissions

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