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.

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
Else
lCnt = lCnt + 1
End If

If lCnt > lMax Then
lMax = lCnt
End If
Next rCell

MaxConsecutiveDays = lMax

End Function

Quote from Carim

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.

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!

Quote from Carim

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.

Quote from Carim

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.

Quote from Carim

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

Display More

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

Quote from Carim

OK ...

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

See attached proposal ...

Let me have your comments

Display More

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.