Pick From Range At Random

  • Pick From Range At Random 0

    1. No (0) 0%
    2. Almost (0) 0%
    3. Yes (0) 0%

    I haven't seen a solution like this on any posts...where you're randomizing a list based on a count set for them without blanks and without changing when refreshed. I'm interested to see what someone comes up with...


    We get several sets of season tickets to various events to be distributed amongst several managers based on the mgr's headcount. So mgr1 may get 20 dates while mgr2 gets 5. All this info is calculated on Sheet1, but the only info that is important for this exercise is the mgr's names and how many dates they get. Available Dates will always be the same as Total Headcount.


    Short of writing names on sheets of paper and drawing names from a hat, I would like to automate this process w/ a push of a button.


    Sheet1 - this is where the managers are listed and their Headcount is calculated.


    B C
    10| Mgr1 Mgr2
    12| 20 5


    Sheet2 - this is where my ticket information is listed and will change based on different events.


    A. B. C. D.
    1| Date Row Seat Mgr
    2| 1/1/08 H. 1-4. Mgr1 (20 times)
    3| 1/1/08. I. 1-4. Mgr2 (5 times)


    Ideally I would like to place a button on Sheet1 or 2 that would execute a code that looks at the range of Mgr's names and based on their hdct, inserts their name THAT many times in column D of Sheet2 like the above example.


    This information should not change once it's assigned or unless the button is pushed again. The number of Mgr's names in the row on Sheet1 will change, but is never be more than 15, so the code must skip over cells that are blank if referencing, for ex. B11:P11.


    Thanks!