Random draw of a random draw

[Lawrence429]

Hi all --
I am working on an audit schedule and I need to cover 4 divisions and each divisions has multiple areas. Something like this (disregard periods):

Randomly select division, then randomly select Task from that division for instance Green is picked, then k is picked from the "Green" list. All tasks are finished within a 30 day period. I am not very good at VBA so if we could stay with equations it would be greatly appreciated.

Thank you much




DivisionTasks
Blue..BlueGreenRedYellow
Green..aimt
Red..bjnu
Yellow..ckov
...dlpw
...e.qx
...f.r.
...g.s.
...h..

 

[Chihiro]

So you need random sampling? I'd recommend uploading sample file.

But the concept should be something like below.

1. Have random number assigned to each Division. Since, it's volatile it will assign different number each time something changes.
=Rand()

2. Have rank column. Assuming divisions in Column A, rand in Column B
=RANK(B2,$B$2:B$5)

3. Use VLookup or INDEX, MATCH to retrieve where rank = 1
=INDEX($A$2:$A$5,MATCH(1,$C$2:$C$5,0))

4. Set up task list for each division and do same set up as above.

See attached for sample. Each recalculation will pick new division and task.

For something more robust, you'd probably want VBA solution.

 

[Lawrence429]

Hi Chihiro-- this is pretty slick Thank you
Two questions, how do I know all questions are answered in a given time period and how do I refresh it? Currently I click on a formula cell and that recalculates everything; I would like to create a "button" that someone could press to have this done as I will be locking down access to the cells with equations.

 

[Chihiro]

Is it for random selection of questions for testing? Then, you don't want formula method. But use VBA to randomize things.

I'll need sample workbook set up, along with any constraints etc, and expected output. To help you code.

If unable to... try applying methodology used in thread below to your purpose.
https://chandoo.org/forum/threads/random-sample.37106/#post-222975