Randomly relisted a group of names onto another Book
I have a list of Names that I need to regenerate randomly onto another book (tab). Say there is 50 names on one page, each name on a1, a3, a5, b1, b3, b5, etc. I need each of these 50 names to be generated to a specific cell randomly on another workbook. I also need to be able to execute this action with a button that can be pressed on the workbook (if possible).
If anyone knows how please help.
The attached is one method, where a sheet of Names in A1-B1 to A59-B59 is accessed via an offset =OFFSET(Names!A$1,(E1*2-2),0) based on a random sequencing of the numbers 1 to 30 via the =INDEX(ivert,COUNTIF(rvert,"<="&INDEX(rvert,ROWS(F$1:F1))))
I presumed that the A1-B1 were two parts of a name, as per A2-B2 etc.
To increase the range beyond 30 make sure the Named ranges also increase.
Press F9 (Calculations set to manual) to re-generate a list.
Originally Posted by Don Juan
nice, but one question
favor to ask. Those clusters of formulas are brilliant but uncomfortably confusing. When I tried to input all that into my worksheet, lol, well anyway, can I send you my workbook and possibly have you set up the formulas in it as you did the last?
the actual names on the roster are on the tab called Roster Sheet. The names are randomly generated to the watch bill tab. The Roster Sheet carries names from a12:a50. They will be generated to the Watch Bill in cells c15, c17, c19, c21, c23, c25, e15, e17, e19, e21, e23, e25, g15, g17 etc. Every other cell both vertically and horizontally until it reaches s25.
Now on the roster sheet I am using the columns, a-v, so if its possible to put all the calculations underneath all that starting at a70 and on just as you had it before that would be cool.
Perhaps you would consider letting me email you a copy of the notebook. I tried to attach it here but it wouldn't let me. You'll be able to see what i'm trying to do. Heres where im a little concerned though. You hooked me up with the basics on generating the names but there are other considerations. Such as, look at the roster sheet where the boxes are labeled Restriction, Unqualified, Duty day off, fire trainer/gas chamber, and mando. The names in those boxes are also on the roster list (roster list is the list being generated to the watch bill) in the a column (the names in there are for example), but cannot be put on the watch bill..... with the exception of mando, which can only be placed on the watch bill from 2145 to 0500 on the week days and anytime on the weekends. I'm sure the other names, (duty day off, unqualified etc) wouldn't be that hard but the mando is a little tricky. What do you think? Whatever you could do for me I would really appreciate. This project is going base wide in IL for the navy. Big thing.
Sounds like the easy way for the first portion is to copy the Shuffle sheet into your workbook, and change the formula in A1:B1 to your sheetname:your columns etc.
Then in the cells that you need the list simply pick up the value generated in the Shuffle sheet, your sheets are kept simple and the Shuffle, once set up, can remain hidden.
If you forward as per the email I will take a look.
Originally Posted by Don Juan
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1