Find people not on leave and create a list

  • Hi to all

    What I am trying to do is search the leave calendar (sheet APS), find those not on leave (blank or P (P is in white so not visible)) from the APS sheet, between the two dates specified on the Duty Picker sheet, and list them in on the duty Picker sheet.

    I am not sure where to start.

    Any assistance would be muchly appreciated.

    I have attached the example workbook that I am working on.


    Example Leave Planner.xlsm

  • Hello,


    Thanks for your file :)


    Unfortunately, you have structured your Planner without a worksheet dedicated to holding the Database ...


    By combining Data with "Cosmetics" ... you are making your life a lot more complicated ...8o


    Will try to see if a twisted patch could help ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    In order to illustrate your objective, could you manually input your expected final result in the Duty Picker sheet ...


    If you were to pick, for example, Date From : 1 05 2021 and Date To : 7 05 2021


    which means, you would have to scan the Range DV3 - EB95


    see attached

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks Carim, I did think database when i first stared but decided against it... we all nee a challenge :)

    I have updated the sppreadsheet with the answer. basically its a filter between two dates and looks at P or Isblank, then returns all the corresponding names, etc.

    Test padawan.xlsm

  • Thanks a lot for you test file ... which clearly illustrates your objective :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Forgot to mention that if you want to get rid of both cells C6 and E6 in sheet ' Duty Picker ' ... just let me know ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • OMG Carim, you are a genius, why didn't I think about an autofilter. It seems to work well. Thank you so much.

    I'll keep the cells and simply change the font colour to white.... easy pezy

    Just to be cheeky, how would I bring back this information minus hidden rows?

  • Glad to hear this is helping you out :)


    Thanks a lot for your Thanks ...AND for the Like :thumbup:


    Regarding your last question, what exactly do you mean by ' minus hidden rows ' ... since it is exactly what the macro does ...

    bringing back " only the visible rows " ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • actually it seems to return the already hidden rows that meet the criteria too? down the left you will see S and A in white font, I have code that hides rows with the S.

    It does the same thing if i autofilter the names and then remove the filter

  • OK ... understand now your remark ...;)


    For AutoFilter, your hidden rows are `visible ' for selection ... since Hidden Rows and Filtered Rows are two independent concepts


    Hope this clarifies

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Glad to see you have all fixed it :)


    Thanks again for your Thanks and Like :thumbup:


    Just marked your Thread as Resolved ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)