Cycle through Table to update another table based on table value

[kengooch]

So I have 5 staff members that are each assigned patient cases based upon the last name of the patient, but their assignments will also be calculated off of the total cases. So if there are 94 cases, each staff member gets 18 cases. So I calculate the total staff cases load automatically in column C and each time a staff members case load exceeds the total cases per staff member, conditional format highlights the cell and the If conditional statement resets the count. The Problem is that I need for the sheet to also cycle to the next Staff member once they exceed the total average cases assigned. I have manually changed the equations in column F to work correctly by change the cell value that represents their staff ID. So each time the staff member exceeds the total cases, I have changed the last value in the equation =IF(C10>=C9,D9,IF(C10<C9,$B$3)) to the next field... ie $B$3 to $B$4 to $B$5 etc. Is there anyway to automate that process so that when the count exceeds the value in B38, that it will not only restart the count for the next staff member, but it will then cycle to the next staff member inthe tStaffID table? I have attached a sample workbook with what is currently in place.

 

[Uncle Gizmo]

I noticed that your post has yet to receive a reply. ..

Hence I have bumped it up the list so that others may view it and you may get an answer.

 

[Darrell]

I had a quick stab at this and this might get you started towards the solution that you're after.
It works using the Worksheet_Change event based on the input of patient numbers in column B.