sumproduct with dynamic cell range

[jb]

Hello Helpers,
I have an excel file with 2 sheets. supervision_chart and individual supervision. supervision_chart has date wise list of supervisors.
I need to generate supervision chart for each supervisor in individual_supervision sheet.
When I type supervisor code in B5 cell of individual_supervision sheet, it will fetch the data supervision_chart and display tick mark in front of date if his/her name is mentioned in the list of supervisors for that particular day.
I have successfully done this. But I need to generate E and F as helper column.
I have used then in formula in b10 to b20 in ndividual_supervision sheet.
When I am trying to replace E10 and F10 in formula of b10, it gives error.
Can you help me in removing helper column and do this using single formula in b10 to b20?
Please help.
Attaching sample file.

 

[bosco_yip]

In C10, formula copied down :

=IF(SUMPRODUCT(0+ISNUMBER(FIND(B$5,INDEX(supervision_chart!B$7:L$19,0,MATCH(B10,supervision_chart!B$6:L$6,0))))),"√","")

or,

=IF(COUNTIF(OFFSET(supervision_chart!A$7,,MATCH(B10,supervision_chart!B$6:L$6,0),13),B$5),"√","")

 

[jb]

Thank you bosco_yip for quick help.
both formula worked
Just want to know for knowledge:
In my solution, why it was giving error when I tried to replace E10 and F10 in formula of b10 ?