Check button to run through all tables

  • The spreadsheet is a roster that I have to fill the cells with "07:00-19:00" or "19:00-07:00" which is the only two different shift times we have.


    I wanted to create a "check" button that would run through all tables and display a message if any of the following is false:
    - Monday (day) 5 employees one letter + 2 employees double letter
    - Monday (night) 4 employees one letter + 2 employees double letter
    - Rest of the week (day/night) 5 employees One letter + 1 employee double letter
    - No one worked for more than 4 days straight.

    I managed to think on the function bellow for the 4 days straight rule, but I couldn't think on something for the other 3 rules.
    PS: I put the same info at all the weeks into the spreadsheet just to test while coding.

    Files

    • Test.xlsm

      (20.52 kB, downloaded 59 times, last: )
  • Hello and Welcome to the Forum :)


    You have not arranged your workbook in the most efficient way ... for all your future needs ...


    You could have in a dedicated sheet your database ... and in another sheet your "cosmetics" report ...;)

    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,


    In order to try to find a way to enable your Verification process ... despite the sheet structure ...


    Have added a Formula in Columns AF,AG, AH to check the consecutive shifts ...


    Hope this will help a little ;)

    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:)

  • You could have in a dedicated sheet your database ... and in another sheet your "cosmetics" report

    I actually thought about it a few weeks ago when I've been asked to do it. Originally, there are 3 other locations that has to be included on the workbook, but I thought sorting for one location first and then implement the other two into the code would be easier.

    My idea to the Project, is having a database with the Employee names, Location, Day/Night shift and then the result sheet with the actual Roster that would gather all the info from the database. No Idea on how to do that but I'll figure one by one.

  • Seems to me your initial explanation is pretty clear ...and you are already laying the groundwork ...


    As an initial step, you should draft an Excel sheet with as Headers (in Row 1) all your entities ...


    i.e all the fields you know you will need to track : Location, Employee Name, Shift Type, etc ...


    The various reports, summaries, pivot tables will come in a second step ...;)

    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:)

  • I'll get the actual spreadsheet and send it. Then I'll have another 3 weeks to work on the database and the report.

    For now, I'll use your example. Thanks a lot!

  • You are welcome :)


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

    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:)

  • Once you have checked the file ... feel free to share your comments

    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:)

  • Once you have checked the file ... feel free to share your comments

    Sorry for the late delay. I had a few ideas after checking your file, but I believe I'll have to use the Power Query option, which I don't know much.
    I was thinking on connecting the Week columns from Holidays to the Calendar tabs, then the Employee columns from Employees tabs to Holidays. The results might not be what I expect, but I'll try to sort a Pivot table from there. I was expecting something like this Format, which I'll have to change a few details but at least gave me an idea.

    Files

    • Database.xlsm

      (34.62 kB, downloaded 50 times, last: )
  • Hello,


    Attached is an example of the requested format Shift Scheduling ...:)


    Hope this will help

    :)

    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:)

  • Hello again,


    If this is actually the reporting format you do need ... you can always work backwards ... and design all your Source sheets with the data input designed to feed into this format ...:)

    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,


    Just out of curiosity, could you describe your process to gather the input data ...


    Is there some kind of validation at the initial stage ... or not ...?

    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,


    Just out of curiosity, could you describe your process to gather the input data ...


    Is there some kind of validation at the initial stage ... or not ...?

    It's quite a manual process, which I'm trying to avoid. It was always a roster on top of the previous one. The other person used to get the information as it is like in the first sheet I've attached, then he would manually sort the employees and shift times between the next 3 weeks for all the locations on top of it and send it. It takes too much time, because you have to verify if the employee has been working for more than 4 days straight, if he is in holidays, if there's anyone with more than 48h on that week. Then the number of employees may vary, depending on the location and shift time.

    The first time I looked into it, I knew it could be done in an easier way, but I don't know how to get there. VBA was my first shot, then you've mentioned a database with all the information. I tried using the Power Query thing but I can't create a Pivot Table on the same format as it is in the first attachment (they've just told me It has to be close or even the same format, so I'll forget the one I've linked before). Then I tried to input values manually on the table but it's not possible.

    Files

    • Database.xlsm

      (151.47 kB, downloaded 45 times, last: )
  • Hello,


    Thanks for the sample file ...


    BUT .... in terms of process ... a key step seems to be missing from Sheet 1 :


    How are Employees assigned ... if there is no common ' index ' between your sheets ...???

    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:)

  • I've merged the Employee column from Employees tab to the Employee column from Holidays tab.

  • OK ...


    But in terms of your process ... still missing the ' Who-When ' relationship ...;)


    See attached proposal ...


    Let me have your comments

    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:)

  • I thought on linking the location with employees, but I might need them on different locations and time, in case of emergency, so I didn't proceed with that because I wasn't sure if I could change their location manually later on. Also, I was thinking on a filter for both locations and shift periods (Day/Night) so I can manage them easily but without getting confused. Looks way more simple than I imagined. The Double Click function saves loads of time. With this format, I think I'm able to generate the roster the way they want.

  • Glad to hear the Double-Click can help you out ...:)


    The question is now to determine how the database should be stored ... and what kind of history would you need to keep track of ...

    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 Antonio,


    Could an improved double-click feature based on Rotating Shifts be of any help ...


    It could speed up your input process, allow for quick modifications, and prevent input mistakes ...:)

    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:)