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]

I have just seen this, but I am just about to go out, so I will have to have think about this later.
One question is person 14 the only person who works in more than one building or are there others, or can anybody work in any building

 

[Semi_Competent]

No worries, there's no rush, you've helped so much already. There would be a few people who are unassigned but are normally scheduled to 2 or 3 buildings (where there's gaps between the regular two people), and there's a handful who just fill in the empty spaces.
Also, is there a way to block out certain dates so they won't automatically get filled? For instance, add an exception that the next weekend one building doesn't need cover and anyone who was scheduled there can be moved elsewhere? Moving can be done manually, but can it be locked or set in a way that macro wouldn't overwrite certain cells?

 

[offthelip]

In response to your query about unassigned staff , I think the easiest way of dealing with this is on Rota1 unassigned staff would have their name in row 1 just like the other but would not have a building name in row2, Then when you fill in a D or a N in their column this would be copied to a column on rota3 which is "unassigned staff" it makes sense to have this somewhere on the left of worksheet so it is automatically available. To assign them to a building you would have to do this on rota 3, however the building name would not show up on rota 1 for these people. The software needs to change to be able to assign these people without the check on the building name.

Also you queried the possibility of automatically assigning staff to blank cells. At the moment this system doesn't have any "intelligence" i.e it doesn't make any decisions it just copies data from place to place. Adding intelligence is a much more difficult thing to do , it is possible to add it later by adding some "planning" macors which are run only on demand by your planning people. I suggest leaving this at the moment
Another way to approach making the planning easier is to add some "checking" in the existing macors to avoid the common mistakes. You would need to identify what checking is needed
One check which I have spotted myself is : if you enter a slightly misspelt name on rota3 the current software doesn't warn you and it doesn't enter anything on rota1. I think it would be sensible to add a check on the rota3 software to check that any name entered does exist on rota1 and warn the user of the fact if it doesn't exist.
Finally in answer to the query about blocking out some buildings, I think the way to do this is to use a key word like "Blocked" and when this is entered on rota3 this is then copied to every column on rota 1 that has got that building on it.
All of this is possible . I don't know whether you want to make the changes yourself, I can certainly do them but I can't make any promises as to when but it is more likely to be days rather than weeks.

 

[offthelip]

try this version of the rota 3 software, I have added some checking, it checks to see if the text entered is either a name from rota 1 or "OT" or "Blocked" any other entry will throw a messagebox.
I also have added a check to see if the person name you have entered is not normally assigned the building in the column on rota 3, once again it throws up a message box. Neither of these warnings change the functionality but make it a lot easier to use. And will hopefully avoid some errors.
I haven'#t looked at unassigned staff yet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("d5:ai340")) Is Nothing Then
    ' Pickup all the data 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))
     pname = Target.Value
     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 (Target.Value = " OT" Or Target.Value = "Blocked" Or nameandbldgfnd Or Target.Value = "") 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 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
   
    End With
End If
End Sub

 

[offthelip]

I have just had a thought about the unassigned staff is it not possible just to have a column (or columns) where the building name is "unassigned 1" , "unassigned 2", etc. then the functionality is just the same

 

[offthelip]

I forgot to say I have also expand the "operational area" to D5 to AI340, this doesn't seem to have any impact on the speed of response.

 

[Semi_Competent]

I'd love to make the changes myself but my knowledge of vba is still very limited; I'm in the middle of a 20ish hours long course so hopefully that will improve soon though. In the meantime I'm grateful for any adjustments that you have made.
Regarding unassigned staff, just to make sure I'm understanding it correctly, are you suggesting having extra few columns on the side of the rota 3 and then manually filling in the blanks? Would dragging and dropping work fine and not get overwritten by macro (assuming there's no clash)?
I will start testing most recent code and report back.
Thanks again!

 

[offthelip]

I don't think dragging and dropping works but copying and pasting does

 

[offthelip]

I just tried copying and pasting a group of cells and it creates an error so that will need to be dealt with. Copying and pasting a single cell works Ok

 

[Semi_Competent]

After testing the code for Rota3, I've noticed that if I put "Blocked" status on sheet3 and someone was scheduled to work that shift, their record would be deleted from Rota1. I know it's likely by design because if I manually delete a shift on rota3 it carries through to rota1 but is there an option for it to behave differently if the word "Blocked" has been entered? For instance, if "blocked" was entered and Person 12 was due to work there, it would instead be copied to a column on the right from rota3.