• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sumproduct with dynamic cell range

jb

Member
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.
 

Attachments

  • schedule.xlsx
    13 KB · Views: 4
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),"√","")

74751
 
Last edited:
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 ?
 
Back
Top