#1
|
|||
|
|||
Formula Help Required
Hi All I am looking for help to produce a formula that will tell me if the same person is assigned to work on two projects at the same time. A sample worksheet is enclosed. Thanks for anyones help. Tony (OTPM) |
#2
|
|||
|
|||
well, this could be an option...
=IF(K4>0;COUNTIFS(C$2:C$10;C4;K$2:K$10;">0";H$2:H$10;H4);IF(L4>0;COUNTIFS(C$2:C$10;C4;L$2:L$10;">0";H$2:H$10;H4);"")) but it starts counting if the project name, task name and start date are the same; if you do not want to take into accout the start date, just remove the red refferences from formula.. there are 2 alternatives, in sheet 1 and sheet 2, attached. |
#3
|
|||
|
|||
=countifs($d$2:$d$10,d2,$c$2:$c$10,c2)
|
#4
|
|||
|
|||
Hi Both
Sorry for the delay in responding. Thanks for the suggestions so far however it does not give me what I need. Perhaps I did not explain clearly enough. Basically I need to return an answer of "Yes" if the same resource is assigned to tasks on different projects (Column C) during the same time periods Columns H and I. Hope this makes it clearer. Kind regards Tony(OTPM) |
#5
|
|||
|
|||
I think this will do what you need:
=IF(COUNTIFS($D$2:$D$10;D2;H$2:H$10;H2;I$2:I$10;I2 )>COUNTIFS(D$2:$D$10;D2;C$2:C$10;C2;H$2:H$10;H2;I$ 2:I$10;I2);"Yes";"") but you will get an Yes for duplicate entries too... Anyway, I guess in the real table there will be no duplicates (or you will use data instruments-remove duplicates). If there will be duplicate entries, then we need to extend the formula... |
#6
|
|||
|
|||
Hi Catalin
Many thanks for the formula, all I had to change was all the semi-colons to commas and it worked fine. If only internal departments in my company could work as efficiently and effectively as this forum does. Aaaahhhh we can only dream can't we. Many thanks for your help. Tony(OTPM) |
#7
|
|||
|
|||
Glad it worked...
Well, i have a topic too, but still no answer yet...maybe the problem it's too difficult |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sum required without consider the negative amount | PRADEEPB270 | Excel | 2 | 08-22-2011 04:23 AM |
Help required with spacing | rohanmalhotra | Word VBA | 3 | 08-11-2011 04:06 AM |
Help required with mobile powerpoint | scrichens | PowerPoint | 0 | 05-17-2011 02:58 AM |
Suggestion required | domex | Word | 0 | 10-06-2010 05:35 AM |
Urgent Help Required | id12345 | Word | 4 | 12-29-2009 08:22 AM |