Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Class Helpers- Rotating List?

0

Hello,

I need a way to make a template for a teacher wanting to rotate children through a list of classroom helper 'jobs' weekly throughout the school year so all students get a turn at each job and it would start at the beginning after completing the list.  There are about 20 students each year and 12 jobs.  Is there a way to set it up without knowing exactly how many students (to skip blank cells)?  I can't think of a way to set this up.  Any help would be greatly appreciated.

Answer
Discuss

Discussion

You need to think about the rules of the system that you want to create. Can multiple students work the same job at the same time? How many weeks should this work for? Can a student do the same job more than once in the year? Etc. These are the same rules you would have to tell the teacher in charge as well as the templating system you are creating.

However, you could just setup a template and tell them to fill it out themselves and that they should select each job at least once and according to the rules of how it should work.
don (rep: 1989) Jun 8, '18 at 6:43 am
No, just one student per job per week.  Yes, the same students could do the job again only after everyone gets his/her turn.  A rotating list maybe, so the teacher could click week 1, 2, 3, etc.  Or even something that changed weekly by date?
jehinkley Jun 9, '18 at 1:05 am
Look at the sample i provided in the answer to see if that's in the right direction.
don (rep: 1989) Jun 9, '18 at 4:37 am
This is more complicated than I thought. I'm chewing on it. Please watch this space!
Variatus (rep: 4889) Jun 9, '18 at 9:06 pm
Agree with you Variatus! I kind of copped-out and put a simple-but-not-great solution up before confirming this was the right direction to go in. It's one of those things that is easy to conceive but not exactly put into a working format without some thought.
don (rep: 1989) Jun 16, '18 at 1:58 am
Add to Discussion

Answers

0

Is this kind of what you're looking for? Where the numbers are for the jobs.

Discuss
0

Finally, I got all the kinks I could think of ironed out. For further testing please find the resulting file attached. Operation is easy. Just double-click on any of the weekly captions, Wk 01 and up in the attached workbook. The assignments will be entered in the column below the clicks.

Note that the system looks for previous assignments to the left of the column currently being filled. If you fill columns out of turn there will be logical irregularities.

You can rename the captions in any way you like. The program doesn't look at the captions, just the location you clicked. It's the same with the students names and with the job names. Any number of students is OK, and any number of jobs (but not more jobs than students!). You can rename the worksheets, too. There are more instructions on the JobsList tab.

The logic I implemented is to assign jobs in random order. Any student can get any job or non at all. Preference for job assignments is given to students who had fewer jobs in the past; random selection among equals. Finally, when assigning individual jobs, preference is given to students who didn't have that job before, failing which, a student who had the same job the longest time ago. Not much remains truly random after all these rules are applied but enough to make it difficult to predict the next list of assignees.

This is a macro enabled workbook. Your security settings must permit the running of macros. The code supports most possible layout changes you might want to make to the worksheets. You will find some instructions and explanations in the code. Look for the code in the code sheet pertaining to the Assignments worksheet. All the code is in that module. There are no parts of the code anywhere else.

Discuss

Discussion

Did you test my solution yet? Let me know if you have a problem getting the macro to work.
Variatus (rep: 4889) Jun 13, '18 at 8:08 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login