• 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.

Auto Allocation of Task based on preference and accumulative hours

B

birkoracing

New Member
Hi


I am having difficulty creating a formula that automatically assigns tasks to staff based on who is best suited to the task until they have a full schedule which is greater than 8.5 hours. Unfortunately the way I have written the formula results in a circular reference and I can’t find a way around it.


I have a table named (WorkOrders) which has Column A as where I need to populate from the formula or macro, Column B which is a job number, Column C which has a list of material numbers and Column D which is the remaining work needed in hours.


In another table named (SkillsMatrix) I have a skills matrix which has a preference of which staff member to use based on different material numbers.

I’m trying to find a formula or macro that assigns the preference 1 staff member for that corresponding material number until they exceed the “Max Work Hours” of accumulative work then assign priority 2 staff, until they exceed 8 hours and so on and so on.


I tried having a SUMIF running total and then having an IF formula look at this but I ended up in a circular reference which I understand.


Any help or suggestions would be much appreciated. I have seen a previous post and solution under the below link which is very similar to what I need however I wasn’t able to modify the macro in a way to make it work.

http://forum.chandoo.org/threads/allocating-work-based-on-sum-of-numbers.26299/


Thanks
 

Attachments

Hi ,

An interesting problem , but one which has no solution , at least in this case !

The total number of hours to be allocated is 100 ; the number of employees is 9 , which when multiplied by 8.5 is much less than 100.

What is to be done with those hours which cannot be allocated for want of staff ?

Narayan
 
Hi

Thanks for your reply. In actual reality there would be more staff to select from but if this case was to arise it be fine for it to be unallocated and display "unallocated" in that field.
 
Hi Narayan

Wow that's great thanks very much for your help. Is there any way to get the name of the staff allocated populated in the Column A against the corresponding order numbers?

Thanks

Daniel
 
You must log in or register to reply here.
Back
Top