You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

- Thread starter jdcalamia
- Start date

Hello

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

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:

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

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.Hellojdcalamia,

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

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:

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:

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

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

As a result, theData Validationdialog box will appear. Go to theSettingstab >> Set theAllowoption toList>> Set theSourcetoSettings!$C$5:$C$52(employee names/IDs range) >> ClickOK.

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

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

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

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:

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

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:

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:

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

Now, go to the

Then go to the

Now, press

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

Code:

```
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

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

Thanks for reaching us. From your problem description, I understand that you want the following pattern:S >> S >> S >> OFF >> L >> L >> OFFIt seems like your employees follow a pattern of 3 consecutiveSshifts, a day off, 2 consecutiveLshifts, 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 (forSshift) or 2 (forLshift) 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 Shiftvalue isS, only the following pattern will be used:S >> S >> S >> OFF >> L >> L >> OFFIn other words,S >> S >> OFF >> L >> L >> OFF >> SorS >> OFF >> L >> L >> OFF >> S >> Spatterns are not possible.

- If the
First Shiftvalue isL, the following pattern will be used:In other words,L >> L >> OFF >> S >> S >> S >> OFFL >> OFF >> S >> S >> S >> OFF >> Lpattern is not possible.

- If the
First Shiftvalue isOFF, then the following pattern will be used:In other words,OFF >> S >> S >> S >> OFF >> L >> LOFF >> L >> L >> OFF >> S >> S >> Spattern is not possible.

Now, go to theSettingssheet and set the shift options toS,L, andOFFas shown in the following image:

Then go to theRostersheet and chooseFirst Shiftfor all employees.

Now, pressAlt + F11to open theVisual Basic Editorwindow. Click theInserttab and selectModule.

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

Code:`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 theRunbutton.

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

Regards,

Eral

Student

DearThank 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

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:

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

Code:

```
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
```

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

put Shift S2 then Fri and Sat weekoff with their leaves how can I do please help me

You can use Excel's IF function to assign week-offs based on the shift type.

For Shift S1 (Sat and Sun off):

If you also want to manage leaves, you can add another column to track leave days and incorporate it into your logic.