Ideas on how to improve and streamline rota in Excel?

[Semi_Competent]

Hi Everyone,

Oh boy, where do I start. I have inherited a spreadsheet that we have been using as a rota for about 40 members of staff for some time now. We were meant to find software that would be more suitable but with Covid-19 any funds that might have been allocated towards that project have disappeared and that might be the case for the next few years so we will be using Excel for a while longer.

I have attached screenshots of our current spreadsheet and I’m looking for advice on how to automate it a bit more and make it less human error prone than it currently is. Seriously, I will consider any advice, no matter how small the improvement might be.

In the file there are 3 worksheets:

1. Rota pattern for all staff (usually for a full year)
2. Sheet with the formulas from 1st worksheet to allocate staff to specific buildings. It’s hidden.
3. Sheet that’s used by everyone to check who’s where. Most changes are made here.

Ad 1. The pattern is not exactly regular as it is spread over 7 weeks (don’t get me started…) and there are exceptions to that as well, as there are a couple of staff members that have it adjusted manually. As a general rule, there are 4 days in, 4 days off three times and 3 days in, 5 days off three times. Split by building and day/night shift. "D" in a cell means dayshift, "N"= nightshift, "O" = overtime (updated manually), "H"=Holiday, and "U"=unavailable. Only "D" and "N" have formulas associated with them.

Ad 2. Consists of mostly simple IF functions to translate the first sheet to an easier view per building. It usually isn’t updated much unless there are some mistakes. If a cell it refers to isn’t "D" or "N" it returns blank. Not every building is filled in completely as a number of staff members are unassigned to a location so they can cover those sites that are needing it.

Ad 3. If someone checks the rota, that’s where they go to. Conditional formatting is set up to highlight what shifts are needing cover. Unassigned staff members are dragged and dropped from the right to the building they are covering. This needs to be done manually. Any shift that’s not covered is filled in manually with staff who volunteer to do overtime.

I have copied a portion of a sheet that we were using but deleted some things so it’s not 100% exact copy but it is a good representation of what we’re dealing with. The file is stored online so staff most likely will open it in Excel online but admins will still edit it in the desktop version.


How to make this sheet more streamlined and user friendly? Every time there is a shift swap, for instance, it causes some issues because sheets 1 and 3 have to be updated individually and sometimes things get missed out. I’m really looking for some guidance, as at this stage I don’t even know what would be the correct question to ask on how to improve things. Any advice would be much appreciated.

 

[offthelip]

The fundamental design of the system doesn't make that very easy. The reason being that my original analysis of your problem identified that your two worksheet were displaying the same data just in different ways. I use this fact and the fact that you can only update one sheet at once to implement the system which always copies all of the information from whichever sheet you update to the other .
If you look at the first bit of code in the rota1 worksheet change event it clears out all the data from that sheet .
I will have a think about what would be the best way around this.
What I have done is I have solved the error which was coming up when trying to copy and paste a group of cells on rota3. I did reckon that copying multiple rows was useful and should be allowed so I have put a check in for multiple rows. However I thought trying to copy multiple columns was not sensible and thus should be prevented. I found an easy and neat was of preventing it by adding some code to the worksheet selection change event which stops you selecting multiple columns. So try this new code for rota 3. (No change to rota 1)

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("d5:ai340")) Is Nothing Then
  
    ' Pickup all the dat from this sheet
    rota3 = Range(Cells(1, 1), Cells(340, 35))
    ' pick up building name for this column
     Bname = rota3(3, Target.Column)
    With Worksheets("Sheet1")
     rota1 = .Range(.Cells(1, 1), .Cells(340, 35))
' detect whether more than one row changed ( we prevent more than one column being selected)
     rr = Target.Rows.Count
     For rowcnt = 1 To rr
         If rr = 1 Then
         pname = Target.Value
         Else
         Tarr = Target.Value
         pname = Tarr(rowcnt, 1)
         End If
         namefnd = False
         nameandbldgfnd = False
         ' check whether the name is found on rota 1
         ' and check whether the person is normally allocated to this building
        For nn = 4 To 35
         If rota1(1, nn) = pname Then
          namefnd = True
          If rota1(2, nn) = Bname Then
          nameandbldgfnd = True
          Exit For
          End If
         End If
        Next nn
        If Not (pname = " OT" Or pname = "Blocked" Or nameandbldgfnd Or pname = "") Then
            If namefnd And Not (nameandbldgfnd) Then
             MsgBox (" This person in not normally assigned to this building, so this entry is not reflected on Rota one")
            Else
             MsgBox ("Illegal entry, enter valid name, OT or Blocked")
            End If
        End If
        
         ' loop through all cells and clear out current shifts from the output starting row 5 acolumn 4
            For i = 5 To UBound(rota1, 1)
             For j = 4 To UBound(rota1, 2)
               If rota1(i, j) = "D" Or rota1(i, j) = "N" Then
                rota1(i, j) = ""
               End If
             Next j
            Next i
        ' now loop through the input and write them to the outputs
         For i = 4 To UBound(rota3, 2)
           For j = 5 To UBound(rota3, 1)
               ' so for each cell see if this matches one of the persons
                For kk = 4 To UBound(rota1, 2)
                 If rota3(j, i) = rota1(1, kk) And rota3(3, i) = rota1(2, kk) Then  'we have found the persons name and the building
                    ' Check if this is the entry just made
                   ' check if day or night shift
                    If i Mod 2 = 0 Then
                    letter = "D"
                     Else
                     letter = "N"
                    End If
                    rota1(j, kk) = letter
                    notfnd = False
                   Exit For
                 End If
                Next kk
           Next j
         Next i
            
    
     Application.EnableEvents = False
      .Range(.Cells(1, 1), .Cells(340, 35)) = rota1
     Application.EnableEvents = True
    Next rowcnt
    End With
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("d5:ai340")) Is Nothing Then
    ' prevent selection of more than one column
    cc = Target.Columns.Count
    If cc > 1 Then
    tt = Target.Address
    nt = InStr(tt, ":")
    lt = Left(tt, nt - 1)
    Range(lt).Select
    End If
End If
End Sub

 

[offthelip]

The simplest thing to do is to throw a warning which I have got to work by saving the previous value of the cell by using the selection change event. This sort of technique could be used to write the name somewhere, but your simple idea of just writing to a column on the right is a bit too simplistic,. I have questions:
Which column ?
What happens if there is already some data in this column?
What happens if two buildings are blocked on the same row and you have got two people to copy?
when are these names ever going to be deleted and how do you decide to delete them.
Try this code

Dim previousval As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("d5:ai340")) Is Nothing Then
    If Target.Value = "Blocked" And previousval <> "" Then
     MsgBox (previousval & " Has been deleted from rota1")
    End If
    ' Pickup all the dat from this sheet
    rota3 = Range(Cells(1, 1), Cells(340, 35))
    ' pick up building name for this column
     Bname = rota3(3, Target.Column)
    With Worksheets("Sheet1")
     rota1 = .Range(.Cells(1, 1), .Cells(340, 35))
' detect whether more than one row changed ( we prevent more than one column being selected)
     rr = Target.Rows.Count
     For rowcnt = 1 To rr
         If rr = 1 Then
         pname = Target.Value
         Else
         Tarr = Target.Value
         pname = Tarr(rowcnt, 1)
         End If
         namefnd = False
         nameandbldgfnd = False
         ' check whether the name is found on rota 1
         ' and check whether the person is normally allocated to this building
        For nn = 4 To 35
         If rota1(1, nn) = pname Then
          namefnd = True
          If rota1(2, nn) = Bname Then
          nameandbldgfnd = True
          Exit For
          End If
         End If
        Next nn
        If Not (pname = " OT" Or pname = "Blocked" Or nameandbldgfnd Or pname = "") Then
            If namefnd And Not (nameandbldgfnd) Then
             MsgBox (" This person in not normally assigned to this building, so this entry is not reflected on Rota one")
            Else
             MsgBox ("Illegal entry, enter valid name, OT or Blocked")
            End If
        End If
        
         ' loop through all cells and clear out current shifts from the output starting row 5 acolumn 4
            For i = 5 To UBound(rota1, 1)
             For j = 4 To UBound(rota1, 2)
               If rota1(i, j) = "D" Or rota1(i, j) = "N" Then
                rota1(i, j) = ""
               End If
             Next j
            Next i
        ' now loop through the input and write them to the outputs
         For i = 4 To UBound(rota3, 2)
           For j = 5 To UBound(rota3, 1)
               ' so for each cell see if this matches one of the persons
                For kk = 4 To UBound(rota1, 2)
                 If rota3(j, i) = rota1(1, kk) And rota3(3, i) = rota1(2, kk) Then  'we have found the persons name and the building
                    ' Check if this is the entry just made
                   ' check if day or night shift
                    If i Mod 2 = 0 Then
                    letter = "D"
                     Else
                     letter = "N"
                    End If
                    rota1(j, kk) = letter
                    notfnd = False
                   Exit For
                 End If
                Next kk
           Next j
         Next i
            
    
     Application.EnableEvents = False
      .Range(.Cells(1, 1), .Cells(340, 35)) = rota1
     Application.EnableEvents = True
    Next rowcnt
    End With
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("d5:ai340")) Is Nothing Then
    rr = Target.Rows.Count
    ' prevent selection of more than one column
    cc = Target.Columns.Count
    If cc > 1 Then
        tt = Target.Address
        nt = InStr(tt, ":")
        lt = Left(tt, nt - 1)
        Range(lt).Select
        previousval = Range(lt).Value
    Else
     If rr = 1 Then
      previousval = Target.Value
     Else
      previousval = "" ' if multiple rows written we can't cope!!
     End If
    End If
End If
End Sub

 

[Semi_Competent]

The simplest thing to do is to throw a warning which I have got to work by saving the previous value of the cell by using the selection change event. This sort of technique could be used to write the name somewhere, but your simple idea of just writing to a column on the right is a bit too simplistic,. I have questions:
Which column ?
What happens if there is already some data in this column?
What happens if two buildings are blocked on the same row and you have got two people to copy?

I thought that I would leave enough spaces for the unassigned staff on the right side from the building allocations and have, say 4 columns for those that were scheduled in a building that's now blocked. It would be just a one specific building 90% of the time so that should be enough I think.

 

[offthelip]

That doesn't really answer any of my questions!!