I have a workbook with two sheets (work_load and Assign) that I use to track assigned task for a particular house address. Each address that the workers work at has 3 tasks (Sign, Path & Drop), the tasks can all be assigned to 1 person or 3 different people. Shown below
Screen Shot 2020-09-28 at 3.54.05 PM.png
On my sheet named "Assign", I assign the tasks to the various workers and it's updated on the workload sheet. As shown below
Screen Shot 2020-09-28 at 3.57.56 PM.png
Each worker has an excel sheet which I update with all assigned tasks manually everyday. I want to have a dynamic formula that will fetch all assigned work to each worker only for tasks they are responsible for. As below in the case of John
Screen Shot 2020-09-28 at 4.02.14 PM.png
I am able to generate the report for each worker however, it also displays the other workers responsible for the other tasks associated with a particular address.
this is the formula I used to achieve this:
=IFERROR(INDEX(Work_load!$A$2:$J$20, SMALL(IF(1=((--(Assign!$A$2:$A$20 = Work_load!$F$2:$F$20))) * (--("John"=Work_load!$H$2:$J$20)), ROW(Work_load!$A$2:$K$20)-1,""), ROW()-1),{1,2,3,4,5,6,8,9,10}),"")
Screen Shot 2020-09-28 at 5.57.25 PM.png
Where I am finding a challenge is returning results for workers indicating only tasks they are responsible for. Any help and suggestions are will be very much appreciated. Attached is my file I'm using
Bookmarks