I guess you can close it. Thanks for the help!
I've discussed about it with my peers and we thought that a few ideas should be implemented to the final result. On the "Shift Schedule" tab we thought on 2 more filters in addition to "Month" and "Year", so that way we can manage the employees and shifts throughout the months in an easier way. Also, the table on the bottom showing the names for the specific date chosen, is a nice feature that we want to keep.
The only thing that we see as a problem at the moment is that the final format should be sent close to the format I've attached, which is quite bad to manage everything. On this part, I believe that the VBA would come up to generate the format we need to send, based on the "Shift Schedule" tab.
What if I have a button to check all the statements/rules and then another button to actually generate the spreadsheet?
I don't think we need to track anything, but once we fill in the "Shift schedule" table, we are just gonna leave as it is.
But in terms of your process ... still missing the ' Who-When ' relationship ...
See attached proposal ...
Let me have your comments
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.
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 ...???
I've merged the Employee column from Employees tab to the Employee column from Holidays tab.
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.
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.
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 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.
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.Code
- Public Function MaxConsecutiveDays(ByRef rRng As Range) As Long
- Dim rCell As Range
- Dim lCnt As Long
- Dim lMax As Long
- For Each rCell In rRng.Cells
- If IsEmpty(rCell.Value) Then
- lCnt = 0
- lCnt = lCnt + 1
- End If
- If lCnt > lMax Then
- lMax = lCnt
- End If
- Next rCell
- MaxConsecutiveDays = lMax
- End Function