PDA

View Full Version : Formula to assign resources



ioncila
03-10-2011, 04:48 AM
Hi
I dont know if this can be done using formualas in excel.
In the attached file, there are 2 tables - one for tasks, another for resources.
In the tasks table, I put a resource code in F column. This will automatically calculate, in Resources table, the longest range of time of each assigned resource.
My problem is is I intend to allocate more resources to the same task, as shown in cells highlighted in yellow.

Is this can be done by formulas?

Thanks in advance

ioncila
03-11-2011, 04:34 AM
Hi
I've changed my approach - see "Teste2" sheet:
Separate data in clolumns and changed formula.
However, formula doesnt work.

JimmyTheHand
03-13-2011, 01:33 AM
I would stick to the first approach, i.e. when the multiple resources were indicated in a single cell (Sheet("Teste1")).
The formula in L44 should be: =MIN(IF(ISNUMBER(SEARCH(F44,$F$18:$F$33)),$L$18:$L$33,1000000)) The formula in M44 should be: =MAX(IF(ISNUMBER(SEARCH(F44,$F$18:$F$33)),$M$18:$M$33,0))
It will give you funny results if a resource is not assigned to any tasks, but otherwise I think it's OK.

Jimmy

JimmyTheHand
03-13-2011, 05:25 AM
Forgot to mention that both of the above are array formulas, of course.

ioncila
03-13-2011, 01:58 PM
@Jimmy
Thank you very much for your reply.
However I was about to close this thread because I've got the help I need for my issue.
Still, I have tried your suggestion because it is shorter than the one I've got but, unfortunelly, doesn't work.

For knowing, I solved my problem with this:

"=IF(ISNUMBER(MATCH("*"&$F46&"*";$F$18:$F$33;0));MIN(IF(ISNUMBER(SEARCH($F46;$F$18:$F$33));IF(L$18:L$33>0;L$18:L$33)));")
=IF(ISNUMBER(MATCH("*"&$F46&"*";$F$18:$F$33;0));MAX(IF(ISNUMBER(SEARCH($F46;$F$18:$F$33));IF(M$18:M$33>0;M$18:M$33)));"")
Anyways, Thank you very much
Ioncila