Workers Demand in projects

[amitc4u]

Hi,
I have projects that workers are assigned in, by type of the worker.
I need to create a table of Projects demands by typs of workers.
For each type of worker there will be a list of projects that need this type (marked in "Projects" table by 1) , as shown in "Projects Demands" sheet.
in the attached file there is an example of how the two tables look like (after it was updated).
The assignment will be in the Projects table and the reflection will be in the Projects Demands table.
Thanks,
Amit

 

[Faseeh]

Hi Amit,

Please put this in Project Demand!D7 and execute with Ctrl+Shfit+Enter, drag down and right:

=IFERROR(INDEX(projects!$B$5:$B$34,SMALL(IF((projects!$C$5:$C$34='Projects Demands'!$C7)*(ISNUMBER(projects!D$5:D$34)=TRUE),ROW(projects!$C$5:$C$34)),COUNTIF($C$7:$C7,$C7))),"")

Give it a try

 

[amitc4u]

Hi,
at first it seemed to be working well but when adding lines to the "Projects table", the Demand table gets wrong values (see in the attached file, marked in yellow). also, there is no reference to the dates (months) of the columns. this can cause a mistake.
I added the original "Projects" table because trying to addjust the formula creates mistakes.
Thank's a lot for your help.
Amit

 

[amitc4u]

Hi,
did you get a chance to look at this issue (previouse post)?
I'm waiting for your answer.
Thanks,
Amit

 

[Faseeh]

Hi Amit,

I looked at that when you posted the second last post, i found that you are not updating the ranges, i will further look-into and will revert by few hours. thanks.

 

[Faseeh]

..Needed to update the formula for ranges

=IFERROR(INDEX(projects!$C$4:$C$41,SMALL(IF((projects!$F$4:$F$41='Projects Demands'!$C7)*(ISNUMBER(projects!L$4:L$41)=TRUE),ROW(projects!$F$4:$F$41)),COUNTIF($C$7:$C7,$C7))),"")

Please see if attachment works.