Random Name Selection for Drug Testing

[gonsalvr]

Chandoo et al,

I'm working on a project where I'm trying to build a random name selection tool for one of our medical units that requires drug testing. The randomness is in name selection only. Every name on the list must be tested every month. Since this is a rehabilitation unit, admissions and discharges come slowly but do occur.

ALL NAMES ON THIS SPREADSHEET ARE MADE UP (FAKE, any name that actually represents a human is pure happenstance)

Col A is room number and is primary key (doesn't change)
Col B are the names assigned against those rooms, and changes.
--Note I plan to have Col B as the only area the staff will enter data (Admit/Delete a name)
Cell C2 is just a quick count of names to rapidly assess census
Col C3 to C122 created to generate random numbers
Cell D2 is a quick calculation to identify number of Drug Tests required each day (Mon-Fri). Staff may want to migrate to a seven day test week, but they have not decided.
Col F is my attempt to sort random numbers, and match a name with that random number.
--Having problems getting random numbers to sort properly I think the blank spaces are causing issues. In reality I will have blank rooms so wanted to add that variable and solve any issues blank spaces may bring.
--From the formula in F3, I understand the FALSE outcome, but the zeroes, I'm confused by that one, I made sure numbers are numbers in col C

End State (Desired Outcome)
-Get Col F to list names in a static mode, so they don't change.
- Use VLOOKUP or INDEX to list six names for the work week ad infinitum...
-Only need to show Col A and B and the Calendar (Workweek for the month with names)
--Hide the engine underneath, this staff has no desire to write formulas or code They just want the names to appear and not be a duplicate from day to day for the month.

Am I on the right track? Or should I try to accomplish this task in VBA? I've been working on this for a couple weeks and feel I'm getting closer. I'm still a little flummoxed by the calendar issue. Oh yes, I have never written VBA code.
Thanks for any direction you can offer
Randy

 

[Chihiro]

You should go

See link on how to generate unique random number list (between specified range).
http://answers.microsoft.com/en-us/...t/50e58a5b-c494-4a96-88fa-61b68c9daafe?auth=1

Edit: On second thought, you should go with VBA. As formula will recalculate itself anytime workbook is opened or changed.

 

[gonsalvr]

Chihiro,
I have made some progress. I managed to Create random values and ignore blank cells plus added code/function to cell J2 to stop volatility. The staff just has to change that zero to a one press F9 and then change back to zero and all data is static. I managed to insert a monthly calendar with a drop down list that changes each month. I performed a VLOOKUP on column C so the names are sorted smallest to largest.
Now my last issue: is copying column D four names at a time into each date cell starting with the first four placed into F5:F8. Copying a list in groups is causing lots of research time. Thus far, I'm still stuck. I want to paste Values only, but leave my VLOOKUP formula alone in column C.

-End Result: perform VLOOKUP after each new sorting per month only, then auto-populate the names into the calendar.
Thank you for your previous input, I'm getting into a time crunch with this so decided not to take the time to learn VBA right now, so kept plodding along until I could get a formula to work. I realize this is a little kludgey from an performance perspective, but the staff only has to auto-populate names once a month, then lock it down.
-rhg