[Solved] Creating a roster

[jdcalamia]

I have 48 employees assigned over 4 locations and divided into 3 shifts. I want to create a sheet where I select the shift and the employees assigned to that shift populate to cells divided into their assigned locations. To complicate things alittle more. I need each employee cell to be able to select all of the other employees in case of schedule switches.

 

[Seemanto Saha]

I have 48 employees assigned over 4 locations and divided into 3 shifts. I want to create a sheet where I select the shift and the employees assigned to that shift populate to cells divided into their assigned locations. To complicate things alittle more. I need each employee cell to be able to select all of the other employees in case of schedule switches.

Hello jdcalamia,
Thanks for sharing your problem with us. I understand that you want to create a shift roster. You can read the following articles:

• How to Make a Roster in Excel
• How to Create Shift Roster 24×7 with Excel Automation

These articles contain detailed steps for creating an employee shift roster in Excel. However, there are the following two additional requirements in your desired roster:

1. You also want to add locations along with shifts for employees.
2. And you need each employee cell to be able to select all of the other employees in case of schedule switches.

For these two requirements, you can add two additional data validation dropdowns. To demonstrate the process of adding these dropdowns, I will be using the dataset of Create Shift Roster 24×7 with Excel Automation article and modifying it as the following:

After preparing data, select the range where you want to add the employee dropdown >> go to the Data tab >> select Data Validation.

As a result, the Data Validation dialog box will appear. Go to the Settings tab >> Set the Allow option to List >> Set the Source to Settings!$C$5:$C$52 (employee names/IDs range) >> Click OK.

The dropdown list will now be visible. Select the employee name/ID for each cell.

Similarly, shift locations and first shift values for each employee.

Finally, insert date formats and allocate shifts by following the remaining steps in the mentioned articles.

I have attached the workbook used for this solution in the attachment section. Please share your feedback with us.
Regards,
Seemanto Saha
ExcelDemy

 

[eral]

Hello jdcalamia,
Thanks for sharing your problem with us. I understand that you want to create a shift roster. You can read the following articles:

• How to Make a Roster in Excel
• How to Create Shift Roster 24×7 with Excel Automation

These articles contain detailed steps for creating an employee shift roster in Excel. However, there are the following two additional requirements in your desired roster:

1. You also want to add locations along with shifts for employees.
2. And you need each employee cell to be able to select all of the other employees in case of schedule switches.

For these two requirements, you can add two additional data validation dropdowns. To demonstrate the process of adding these dropdowns, I will be using the dataset of Create Shift Roster 24×7 with Excel Automation article and modifying it as the following:

After preparing data, select the range where you want to add the employee dropdown >> go to the Data tab >> select Data Validation.

As a result, the Data Validation dialog box will appear. Go to the Settings tab >> Set the Allow option to List >> Set the Source to Settings!$C$5:$C$52 (employee names/IDs range) >> Click OK.

The dropdown list will now be visible. Select the employee name/ID for each cell.

Similarly, shift locations and first shift values for each employee.

Finally, insert date formats and allocate shifts by following the remaining steps in the mentioned articles.

I have attached the workbook used for this solution in the attachment section. Please share your feedback with us.
Regards,
Seemanto Saha
ExcelDemy

I have also a question on above example, instead of using S1,S2,S3,OFF,L1,L2,OFF and so on and so forth, is it possible to use, S,S,S,OFF,L,L,OFF ? Appreciate for the help.

 

[Seemanto Saha]

I have also a question on above example, instead of using S1,S2,S3,OFF,L1,L2,OFF and so on and so forth, is it possible to use, S,S,S,OFF,L,L,OFF ? Appreciate for the help.

Hello eral,

Thanks for reaching us. From your problem description, I understand that you want the following pattern:​

S >> S >> S >> OFF >> L >> L >> OFF​

It seems like your employees follow a pattern of 3 consecutive S shifts, a day off, 2 consecutive L shifts, another day off, and so forth.
The mentioned articles in my previous post can’t generate this pattern. This is due to the formula used in those articles can repeat a shift value only once whereas your desired pattern has the same shift for 3 (for S shift) or 2 (for L shift) consecutive days.
Although we can develop Excel formulas for generating your desired pattern, the formulas will be very complex. Instead, we can use VBA to generate the desired pattern in a much easier way.
However, whether we use formula or VBA, there will be a few assumptions. For example, I will develop the VBA code based on the following assumptions:​

• If the First Shift value is S, only the following pattern will be used:

S >> S >> S >> OFF >> L >> L >> OFF​

In other words, S >> S >> OFF >> L >> L >> OFF >> S or S >> OFF >> L >> L >> OFF >> S >> S patterns are not possible.​

• If the First Shift value is L, the following pattern will be used:

L >> L >> OFF >> S >> S >> S >> OFF​

In other words, L >> OFF >> S >> S >> S >> OFF >> L pattern is not possible.

• If the First Shift value is OFF, then the following pattern will be used:

OFF >> S >> S >> S >> OFF >> L >> L ​

In other words, OFF >> L >> L >> OFF >> S >> S >> S pattern is not possible.

Now, go to the Settings sheet and set the shift options to S, L, and OFF as shown in the following image:

Then go to the Roster sheet and choose First Shift for all employees.

Now, press Alt + F11 to open the Visual Basic Editor window. Click the Insert tab and select Module.

A module will open. Insert the following code in the module:

Sub GenerateRosterPattern()
    Set roster_sheet = ThisWorkbook.Sheets("Roster") ' change sheet name to your required sheet
    Set shift_range = roster_sheet.Range("F8:AJ55") ' change range address to your employee range
    Set first_shift_range = roster_sheet.Range("D8:D55") ' change range address to your first shift range
    Set date_range = roster_sheet.Range("F7:AJ7") 'change range address to your date range
    
    Dim SArray() As Variant
    Dim LArray() As Variant
    Dim OffArray() As Variant
    
    SArray = Array("S", "S", "S", "OFF", "L", "L", "OFF")
    LArray = Array("L", "L", "OFF", "S", "S", "S", "OFF")
    OffArray = Array("OFF", "S", "S", "S", "OFF", "L", "L")
    
    Dim arr_index As Integer
    
    For i = 1 To first_shift_range.Rows.Count
        arr_index = 0
        If first_shift_range.Cells(i, 1).Value = "S" Then
            For j = 1 To shift_range.Columns.Count
                If arr_index > 6 Then
                    arr_index = 0
                End If
                If date_range.Cells(1, j).Value <> "" Then
                    shift_range.Cells(i, j).Value = SArray(arr_index)
                End If
                arr_index = arr_index + 1
            Next j
        End If
        
        If first_shift_range.Cells(i, 1).Value = "L" Then
            For j = 1 To shift_range.Columns.Count
                If arr_index > 6 Then
                    arr_index = 0
                End If
                If date_range.Cells(1, j).Value <> "" Then
                    shift_range.Cells(i, j).Value = LArray(arr_index)
                End If
                arr_index = arr_index + 1
            Next j
        End If
        
        If first_shift_range.Cells(i, 1).Value = "OFF" Then
            For j = 1 To shift_range.Columns.Count
                If arr_index > 6 Then
                    arr_index = 0
                End If
                If date_range.Cells(1, j).Value <> "" Then
                    shift_range.Cells(i, j).Value = OffArray(arr_index)
                End If
                arr_index = arr_index + 1
            Next j
        End If
    Next i
    
End Sub

Save the workbook as a macro-enabled workbook and then click the Run button.

The required pattern will be generated in the Roster sheet.

I hope this solution will be helpful for you. Let us know your feedback. The sample workbook used for this solution is below.

Regards,
Seemanto Saha
ExcelDemy

 

[eral]

Hello eral,

Thanks for reaching us. From your problem description, I understand that you want the following pattern:​

S >> S >> S >> OFF >> L >> L >> OFF​

It seems like your employees follow a pattern of 3 consecutive S shifts, a day off, 2 consecutive L shifts, another day off, and so forth.
The mentioned articles in my previous post can’t generate this pattern. This is due to the formula used in those articles can repeat a shift value only once whereas your desired pattern has the same shift for 3 (for S shift) or 2 (for L shift) consecutive days.
Although we can develop Excel formulas for generating your desired pattern, the formulas will be very complex. Instead, we can use VBA to generate the desired pattern in a much easier way.
However, whether we use formula or VBA, there will be a few assumptions. For example, I will develop the VBA code based on the following assumptions:​

• If the First Shift value is S, only the following pattern will be used:

S >> S >> S >> OFF >> L >> L >> OFF​

In other words, S >> S >> OFF >> L >> L >> OFF >> S or S >> OFF >> L >> L >> OFF >> S >> S patterns are not possible.​

• If the First Shift value is L, the following pattern will be used:

L >> L >> OFF >> S >> S >> S >> OFF​

In other words, L >> OFF >> S >> S >> S >> OFF >> L pattern is not possible.

• If the First Shift value is OFF, then the following pattern will be used:

OFF >> S >> S >> S >> OFF >> L >> L ​

In other words, OFF >> L >> L >> OFF >> S >> S >> S pattern is not possible.

Now, go to the Settings sheet and set the shift options to S, L, and OFF as shown in the following image:

Then go to the Roster sheet and choose First Shift for all employees.

Now, press Alt + F11 to open the Visual Basic Editor window. Click the Insert tab and select Module.

A module will open. Insert the following code in the module:

Sub GenerateRosterPattern()
    Set roster_sheet = ThisWorkbook.Sheets("Roster") ' change sheet name to your required sheet
    Set shift_range = roster_sheet.Range("F8:AJ55") ' change range address to your employee range
    Set first_shift_range = roster_sheet.Range("D8:D55") ' change range address to your first shift range
    Set date_range = roster_sheet.Range("F7:AJ7") 'change range address to your date range
   
    Dim SArray() As Variant
    Dim LArray() As Variant
    Dim OffArray() As Variant
   
    SArray = Array("S", "S", "S", "OFF", "L", "L", "OFF")
    LArray = Array("L", "L", "OFF", "S", "S", "S", "OFF")
    OffArray = Array("OFF", "S", "S", "S", "OFF", "L", "L")
   
    Dim arr_index As Integer
   
    For i = 1 To first_shift_range.Rows.Count
        arr_index = 0
        If first_shift_range.Cells(i, 1).Value = "S" Then
            For j = 1 To shift_range.Columns.Count
                If arr_index > 6 Then
                    arr_index = 0
                End If
                If date_range.Cells(1, j).Value <> "" Then
                    shift_range.Cells(i, j).Value = SArray(arr_index)
                End If
                arr_index = arr_index + 1
            Next j
        End If
       
        If first_shift_range.Cells(i, 1).Value = "L" Then
            For j = 1 To shift_range.Columns.Count
                If arr_index > 6 Then
                    arr_index = 0
                End If
                If date_range.Cells(1, j).Value <> "" Then
                    shift_range.Cells(i, j).Value = LArray(arr_index)
                End If
                arr_index = arr_index + 1
            Next j
        End If
       
        If first_shift_range.Cells(i, 1).Value = "OFF" Then
            For j = 1 To shift_range.Columns.Count
                If arr_index > 6 Then
                    arr_index = 0
                End If
                If date_range.Cells(1, j).Value <> "" Then
                    shift_range.Cells(i, j).Value = OffArray(arr_index)
                End If
                arr_index = arr_index + 1
            Next j
        End If
    Next i
   
End Sub

Save the workbook as a macro-enabled workbook and then click the Run button.

The required pattern will be generated in the Roster sheet.

I hope this solution will be helpful for you. Let us know your feedback. The sample workbook used for this solution is below.

Regards,
Seemanto Saha
ExcelDemy

Thank you for the effort on helping and asnwering my query, I appreciate it. Still having a problem when using the VBA, for the example in roster sheet, row 8, empl 1, L2, S, the monthly roster will be automatically fill up based on the VBA, but the problem is, if i want to change shift "S" to "L" the monthly roster for Emp 1 doesnt change, i need to modify again the VBA based on the criteria., I think there is no solution for my query as of now, maybe in future update of excel they can consider that one. Many thanks


Regards,

Eral
Student

 

[Seemanto Saha]

Thank you for the effort on helping and asnwering my query, I appreciate it. Still having a problem when using the VBA, for the example in roster sheet, row 8, empl 1, L2, S, the monthly roster will be automatically fill up based on the VBA, but the problem is, if i want to change shift "S" to "L" the monthly roster for Emp 1 doesnt change, i need to modify again the VBA based on the criteria., I think there is no solution for my query as of now, maybe in future update of excel they can consider that one. Many thanks


Regards,

Eral
Student

Dear eral,

Thanks for your feedback. As your previous post didn’t mention anything about the automation process, I focused only on generating the S >> S >> S >> OFF >> L >> L >> OFF pattern.

However, if you want the monthly roster to change automatically whenever you change the shift of an employee, then you will be glad to know that Excel already has this feature available. You can use the Worksheet Change event in VBA to accomplish this. Here’s how:
​

Step 1: Go to the Rooster sheet >> right-click on the sheet tab >> click the View Code option.

Step 2: Insert the following code in the Visual Basic Editor module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Set roster_sheet = ThisWorkbook.Sheets("Roster") ' change sheet name to your required sheet
    Set first_shift_range = roster_sheet.Range("D8:D55") ' change range address to your first shift range
    Set date_range = roster_sheet.Range("F7:AJ7") 'change range address to your date range
    
    Dim edited_row As Integer
    edited_row = -1
    
    If Not Intersect(Target, first_shift_range) Is Nothing Then
        edited_row = Target.Row
    End If
    
    If edited_row = -1 Then
        Exit Sub
    Else
        edited_row = edited_row - 7 ' as the shift roster starts from the 8th row
    End If
    
    If Target.Value = "" Then
        Exit Sub
    End If
    
    Dim SArray() As Variant
    Dim LArray() As Variant
    Dim OffArray() As Variant
    
    SArray = Array("S", "S", "S", "OFF", "L", "L", "OFF")
    LArray = Array("L", "L", "OFF", "S", "S", "S", "OFF")
    OffArray = Array("OFF", "S", "S", "S", "OFF", "L", "L")
    
    Dim arr_index As Integer
    arr_index = 0
    
    If Target.Value = "S" Then
        For i = 1 To date_range.Columns.Count
            If arr_index > 6 Then
                arr_index = 0
            End If
            If date_range.Cells(1, i).Value <> "" Then
                date_range.Cells(1, i).Offset(edited_row, 0).Value = SArray(arr_index)
            End If
            arr_index = arr_index + 1
        Next i
    ElseIf Target.Value = "L" Then
        For i = 1 To date_range.Columns.Count
            If arr_index > 6 Then
                arr_index = 0
            End If
            If date_range.Cells(1, i).Value <> "" Then
                date_range.Cells(1, i).Offset(edited_row, 0).Value = LArray(arr_index)
            End If
            arr_index = arr_index + 1
        Next i
    ElseIf Target.Value = "OFF" Then
        For i = 1 To date_range.Columns.Count
            If arr_index > 6 Then
                arr_index = 0
            End If
            If date_range.Cells(1, i).Value <> "" Then
                date_range.Cells(1, i).Offset(edited_row, 0).Value = OffArray(arr_index)
            End If
            arr_index = arr_index + 1
        Next i
    Else
        ' Do nothing
    End If
    
End Sub

Step 3: Return to the worksheet and make changes to the first shift values. The monthly roster for that row will be updated automatically as demonstrated in the following GIF:

I hope I was able to solve your problem. Let us know your feedback. The updated Excel workbook is attached below.

Regards,
Seemanto Saha
ExcelDemy