Code / formula to populate data based on Name date and given time.

  • I have the attached spreadsheet, with 2 tabs " Inputsheet" and "Day_view" .. I need help with populating "day_view" sheet based on "inputsheet" ..


    in "Day_view" sheet i need to populate respective team member's ( A4:A10) time bracket (B4:P4), using the data in "inputsheet" "From" (D6) and "Until"(E6) .. based on date input from any cell.


    For ex : i want to populate above date based on date "12-Feb-16", this should ideally return below value for the respective team member.


    [TABLE="width: 941"]

    [tr]


    [TD="colspan: 12"][/TD]

    [td][/td]


    [TD="colspan: 3"]12-Feb-16[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Team Member

    [/td]


    [td]

    06:00

    [/td]


    [td]

    07:00

    [/td]


    [td]

    08:00

    [/td]


    [td]

    09:00

    [/td]


    [td]

    10:00

    [/td]


    [td]

    11:00

    [/td]


    [td]

    12:00

    [/td]


    [td]

    13:00

    [/td]


    [td]

    14:00

    [/td]


    [td]

    15:00

    [/td]


    [td]

    16:00

    [/td]


    [td]

    17:00

    [/td]


    [td]

    18:00

    [/td]


    [td]

    19:00

    [/td]


    [td]

    20:00

    [/td]


    [/tr]


    [tr]


    [td]

    Heike

    [/td]


    [td][/td]


    [td][/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Johannes

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Linda

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Oliver

    [/td]


    [td][/td]


    [td][/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    x

    [/td]


    [td]

    forum.ozgrid.com/index.php?attachment/68048/

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Sophia

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Walter

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Code / formula to populate data based on Name date and given time.


    Hi Rex933,


    Try this formula in cell B5, filled across and down:


    =IF(SUMPRODUCT(--(TRIM(InputSheet!$B$7:$B$30)=LEFT($A5,FIND(" ",$A5)-1)),--(InputSheet!$A$7:$A$30=$N$1),--(InputSheet!$D$7:$D$30<=B$4),--(InputSheet!$E$7:$E$30>=B$4))>0,"X","")


    The TRIM function is needed as the Participant names in column B of InputSheet all have a trailing space.


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!