• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to create staff list dynamically

Status
Not open for further replies.
S

Sam Longstaff

Member
Hi,

Does anyone have a VBA solution for this problem:

I have a staff list with a set order which I use to create a rota. The list rolls over and over filling the spaces in the rota. The positions of the staff (relative to each other) in this list does not change.

When the next new rota is created I start from the person after the last person on the previous rota (placed in the example below "Fraser"). My current solution uses formulas but if the number of staff on the original list changes I need to amend the formulas. This is very time consuming. I'd like a VBA solution that allows me to add/remove names on the call list and creates the new rota list order automatically.


I currently use these formulas:


Call listTo begin the weekday rota
SamFraser
Cheryl=MATCH(E2,call_list,0)
Jackie=@INDEX(call_list,$E$3)
Nicola=@IF($E$3+1>15,INDEX(call_list,($E$3-14)),INDEX(call_list,($E$3+1)))
Graeme=@IF($E$3+2>15,INDEX(call_list,($E$3-13)),INDEX(call_list,($E$3+2)))
Fraser=@IF($E$3+3>15,INDEX(call_list,($E$3-12)),INDEX(call_list,($E$3+3)))
Karen=@IF($E$3+4>15,INDEX(call_list,($E$3-11)),INDEX(call_list,($E$3+4)))
Neil=@IF($E$3+5>15,INDEX(call_list,($E$3-10)),INDEX(call_list,($E$3+5)))
Sarah=@IF($E$3+6>15,INDEX(call_list,($E$3-9)),INDEX(call_list,($E$3+6)))
Lorna=@IF($E$3+7>15,INDEX(call_list,($E$3-8)),INDEX(call_list,($E$3+7)))
Vanessa=@IF($E$3+8>15,INDEX(call_list,($E$3-7)),INDEX(call_list,($E$3+8)))
James=@IF($E$3+9>15,INDEX(call_list,($E$3-6)),INDEX(call_list,($E$3+9)))
Paul=@IF($E$3+10>15,INDEX(call_list,($E$3-5)),INDEX(call_list,($E$3+10)))
Victoria=@IF($E$3+11>15,INDEX(call_list,($E$3-4)),INDEX(call_list,($E$3+11)))
Sharon=@IF($E$3+12>15,INDEX(call_list,($E$3-3)),INDEX(call_list,($E$3+12)))
Morgan=@IF($E$3+13>15,INDEX(call_list,($E$3-2)),INDEX(call_list,($E$3+13)))
Karl=@IF($E$3+14>15,INDEX(call_list,($E$3-1)),INDEX(call_list,($E$3+14)))
 
Sam Longstaff
Do this has any connection with Your previous thread?

VBA to automatically populate a rota / shifts

HI, I'm looking for a VBA solution to automatically populate an on call rota please. The rota should be filled according to the following rules: Only 1 staff member for each day Staff member can't be on 2 consecutive days weekdays are allocated separately from weekend days. This is to ensure...
chandoo.org
 
Status
Not open for further replies.
Back
Top