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]

It would appear to me that the three sheets are all showing exactly the same data just order differently. So the way I would design the system is to put some VBA code into each worksheet change event for each sheet so that when you make a change on that sheet the data is copied directly to the other two sheets. This would allow you to edit any of the sheets knowing that the data was always consistent across the three sheets. So for example if change the person1 Sat 4 cell on rota 1 from "D" to blank the VBA would clear cells D8 on rota2 and F8 on Rota 3, etc etc

 

[Semi_Competent]

Thanks for your reply! You are absolutely right, all sheets show the same information, just presented differently. To be honest, I'm not quite sure why there's a need for the 3rd worksheet but the two different views (sheet 1 and 2) are necessary, as one shows the yearly rota for staff and the other where someone is going to work on the day.
Regarding your suggestion, I'm only a beginner in terms of VBA so perhaps I am missing something but wouldn't this just offer the same result as the current IF functions?

 

[offthelip]

No it doesn't offer the same it offer much more:
firstly it means you can edit any of the sheets , i.e you can enter data or change data by making changes on Rota 1 rota 2 or rota 3.
Secondly you can put checks in the make sure the data meets whatever rules you want to put i.e somebody can't be in two places at once, and you can't misspell the name of anybody.
thirdly it prevents anybody mistakenly over writing one of the equations.

 

[Semi_Competent]

Could you give me an example of code for a single cell that I can build on from? That will hopefully give me a sense of direction and I will know what to research next.
Would you suggest still keeping sheet 1 intact to start with?

 

[offthelip]

Looking closely at your sheet rota 2 and rota 3 look virtually identical the only difference that I cod see is that rota 2 has got two columns for every shift and I don't know why. Thus I Have ignored rota 2 at the moment, and concentrated on starting with Roa 1 and rota 3. I have written a bit of code that takes the inputs made on sheet1 ( your rota 1) and writes them into sheet 3 ( your rota 3). This software nees to be in the workhseet change event for worksheet 1 . the way it works is when something is changed on rota 1 it first detects whether the change is in the rota area if it is, it loads all the data from rota 1 into input variant array (inarr) and then loads all the data from the rota 3 sheet into a variant array (outarr). The first thing it does is delete all references to any of the person 1 to person 16 names in the output
It hen checks the inputs to find which persons have a D or an N against their name and building and then writes back their name in the appropriate slot in the output array. finally it writes the array back to the worksheet. This is the first of the two macor needed the second one is do the reverse. I will have a look at this.
Note I didn't know how big you spread sheet was so for development I just use a range up to S34. This can easily be expanded by change the statement tha pick up the variable arrays at the top and bottom of the code. this is why I have used Ubound to determine the loop sizes everywhere:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("d5:S34")) Is Nothing Then
' Pickup all the dat from this sheet
inarr = Range(Cells(1, 1), Cells(34, 19))
With Worksheets("Sheet3")
 outarr = .Range(.Cells(1, 1), .Cells(34, 19))
 ' loop through all cells and clear out current shifts from the output starting row 5 acolumn 4
    For i = 5 To UBound(outarr, 1)
     For j = 4 To UBound(outarr, 2)
      For k = 4 To UBound(inarr, 2)
       If outarr(i, j) = inarr(1, k) Then
        outarr(i, j) = ""
       End If
      Next k
     Next j
    Next i
' now loop through the input and write them to the outputs
 For i = 4 To UBound(inarr, 2)
   For j = 5 To UBound(inarr, 1)
     If inarr(j, i) = "D" Or inarr(j, i) = "N" Then ' this person is on day or Night shift in this building
       ' so find bulding
        For kk = 4 To UBound(outarr, 2)
         If inarr(2, i) = outarr(3, kk) Then 'we have found the building
           ' check if day or night shift
            If inarr(j, i) = "D" Then
             offs = 0
             Else
             offs = 1
            End If
            outarr(j, kk + offs) = inarr(1, i)
           Exit For
         End If
        Next kk
     End If
   Next j
 Next i
 Application.EnableEvents = False
  .Range(.Cells(1, 1), .Cells(34, 19)) = outarr
 Application.EnableEvents = True
End With
End If
End Sub

Note using merged cells can cause VBA to fail to find matches so you might need to change the layout slightly.

 

[offthelip]

One thing I forgot to mention was the building names and the person names MUST be consistent across the two worksheets because the software does a search to match them for every change

 

[offthelip]

here is the other half of the system, this code writes the inputs form rota 3 into rota 1:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim inarr(1 To 3, 1 To 4)
Dim outarr(1 To 3, 1 To 4)
If Not Intersect(Target, Range("d5:S34")) Is Nothing Then
' Pickup all the dat from this sheet
rota3 = Range(Cells(1, 1), Cells(34, 19))
With Worksheets("Sheet1")
 rota1 = .Range(.Cells(1, 1), .Cells(34, 19))
 ' 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) Then 'we have found the persons name
           ' so find the building
           'Is the same person always in the same building because otherwise we need to search for the building as well
           ' use the fact that the column number are even for day shifts and odd for night shifts
           ' check if day or night shift
            If i Mod 2 = 0 Then
            letter = "D"
             Else
             letter = "N"
            End If
            rota1(j, kk) = letter
           Exit For
         End If
        Next kk
  
   Next j
 Next i
 Application.EnableEvents = False
  .Range(.Cells(1, 1), .Cells(34, 19)) = rota1
 Application.EnableEvents = True
End With
End If
End Sub

 

[Semi_Competent]

Wow, that's incredible. Thank you so much! I'll start testing right away.

 

[Semi_Competent]

Worked like a charm, thanks again.
Do you have any suggestions on what to do with the unassigned staff? Some of them are there to fill in the blanks with no building association but in a few cases, they would work in building 1 their first shift, then building 2 their second shift and building 3 their 3rd one.
Is there a way that Excel would look at person 14 (who's first on the list from unassigned) and if they are scheduled to work a dayshift, look at building 1, if it's already filled by person 1 or 2 then look at building 2 and so on? If that's too complex, can it look at the first available building?