Allocating competent staff to specific roles

[north]

I need your help, MrExcel.
I am using Excel2010.

The workspace consists of specific roles that needs to be filled at all times by competent staff.
There are more workers than roles, and the workers may be competent in more than 1 role each.

For the sake of this example I'll cut the number of roles to 3 and workers to 4:

John: Competent in field A and B.
Jane: Competent in field B and C.
James: Competent in field A.
Joe: Competent in field A and C.

Today's workday:
Field A: James
Field B: Jane
Field C: Joe
Extra: John

Now lets say Jane just called in sick. Im looking for a way to make Excel autopopulate all the Fields.
The fields must be populated according to the competent staff at hand. If there is no competent worker to fill the field, and one cannot be made available by rearranging the other roles, then it should read "Not possible".

Now there are often multiple ways to rearrange the roles, therefore there should also be specified "preferred field" for some workers, so they keep their positions if possible.

I am completely stuck here. Is it even possible in Excel?
I would love any input, even just keywords or theory on the subject that may help me get further.
Thank you.

 

[phantom1975]

I don't have Excel installed on this computer, so I am limited to the advice I can offer. I would suggest that you start by building a table. The headers for each column of the table should be: Employee Name, Available, Field A, Field B, Field C. List each employee in the rows of the table. The "Available" column would be a yes / no to indicate if they are available to work on a given day. By the way, instead of an "Available" column, you could create a column for each day of the week and put yes / no according to their schedule. Once you have your table built, it becomes a bit easier to create formulas to evaluate the table. The results are more likely going to show you who is available and competent in a field rather than picking one name. Otherwise, you can assign a value from say 1 to 100 instead of yes / no in the field to indicate their level of competency. Hopefully this helps you get started.

 

[north]

The spreadsheet I've made now works like this:

The workers competent in each field are listed in named ranges for each field (A, B, Cee).
The workers prioritized in each field are listed in named ranges for each field (PreferredA, PreferredB, PreferredC).
The workers that are unavailable are listed in a separate named range (Unavailable).

A formula checks if the workers are a part of said groups (ranges), and gives them a number:

=IF(ISNA(MATCH(H11;A;0));"0";"1")

Unavailable=0, Available=1
Incompetent=0, Available=1
Prioritized=2, Not prioritized=2
These numbers are then multiplied to give a "Field score".

A formula then picks the worker with the highest "Field score" to work in that field:

=INDEX(Workers; MATCH(MAX(WorkersA);WorkersA;0))


Now here comes the problematic part:

One worker cannot work in 2 fields simultaneously.
Therefore I also made a named range to check if a worker is busy when calculating the "Field score" (UsedA).

=IF(ISNA(MATCH(H11;UsedA;0));"0";"1")

Busy=0, Not busy=1

Naturally, this creates a circular referance, so iterative calculation is turned on (100 max iterations).




The problem:

Excel does not recalculate the "Field score" after placing a worker in a Field when he is eligable for more than one field.

=IF(ISNA(MATCH(H11;UsedA;0));"0";"1") still yields 0.
The result is that Excel places one worker in multiple Fields (John works in both Field A and Field B).


I need Excel to behave like this:
Check eligibility (calculate Field score) -> Place worker -> Recalculate Field score -> Place worker -> repeat until options exhausted.

Instead Excel just does:
Check eligibility (calculate Field score) -> Place worker x2


Possible cause:
From what I've read, Excel saves iterative calculations for last.
This causes Excel to evaluate more than one Field Worker before doing the iterative "Field score" calculations.
Excel is not able to "make a choice" and then run the "variation" into exhaustion. It simply puts the first and best candidate in all Fields.





The question:

What can I do to make Excel recalculate the "Field score" and select new "Field Workers" the way I want?

 

[north]

To better illustrate the problem I uploaded the Worksheet to OneDrive:

https://onedrive.live.com/edit.aspx?cid=C895B13705C9BEDA&resid=C895B13705C9BEDA%21148&app=Excel