How to assign case to staff fairly

Hi there,

I would like to assign case to staff (A-Z) fairly. Rules:

1. assign case in "staff A to staff Z" sequence, each staff will receive case in each round.
2. if staff A on leave, the case will assign to staff B; if staff B on leave too, the case will assign to staff C, and so on. And than assign case back to staff A/B first when on duty, and following back to the original sequence, make sure each staff receive ONE case in each round.
3. in case of have new join staff, let said staff AA, put in the end of the sequence.

I'm not sure how to go about doing this, whether by VLOOKUP or something else, if manually input each time, it will be very confusion.....

Any help will be greatly appreciated!

I have worked on your sheet and made some changes.
In the case assignment date, we have included the date of joining info. and actual case assigned to column as well.
Note: Cell E3 in the above said sheet is different from Cell E4.

E3:

Formula:

Please Login or Register  to view this content.

E4:

Formula:

Please Login or Register  to view this content.

Made some good changes in the Staff Leave sheet, dates and borders work automatically based on the changes in given in the Cell C2 and C3.