CODEWITHSUNDEEP
exceltextexcel-formulapartial
user19352606
user19352606

Reputation: 11

Returning list of projects that are assigned to a person

I’m creating a project management template and I’ve bumped into problem.

I need to return a list of projects to 1 cell assigned to a certain member in a given time period (does project falls in a period or not).

When retrieving project names that are assigned to certain person in a period there is a problem because the range of cells that contains information of enrolled members includes several codes in text form - XX, YZ etc. F:F. Cells that include information about code assembled from 3 columns(based on the type of engagement) that could contain same person several times, cells that in that 3 columns also could contain several codes.

Ive managed to make a formula that retrieves list of projects based on starting date H:H, and deadline I:I in 1 cell:

enter image description here

No luck though with implementing binding to certain members. I’ve tried using another IF cycle:

{=TEXTJOIN(“; “;TRUE;IF($H$3:$H$13<=D$19;IF($I$3:$I$13>=C$19;IF($F3:$F$13=“*XX*”;$A$3:$A$13;””);””);””))}

Result - empty cell

I’ve tried implementing VLOOKUP and COUNTIF but so far unsuccessfully

I would really appreciate any possible solutions or workarounds, thanks

Upvotes: 1

Views: 226

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9932

I think you can use a filter function to get this. I used an example on this google sheet, but it should work exactly the same in Excel. Basically you want to textjoin an array which you filter by meeting multiple and statements (*) along with one OR condition (shown with pluses) over four columns. Double check the columns and ranges, but I think this works.

=TEXTJOIN(";",TRUE,FILTER($A$3:$A$19,
    ($H$3:$H$19<=$D$19) * ($I$3:$I$19>=$C$19) * ( 
       (isnumber(find($B20,$D$3:$D$19))+
       (isnumber(find($B20,$E$3:$E$19)))+ 
       (isnumber(find($B20,$F$3:$F$19)))+
       (isnumber(find($B20,$G$3:$G$19))) ))))

Upvotes: 0

Related Questions