Semi_Competent
New Member
- Joined
- Jun 20, 2020
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
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:
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.
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:
- Rota pattern for all staff (usually for a full year)
- Sheet with the formulas from 1st worksheet to allocate staff to specific buildings. It’s hidden.
- Sheet that’s used by everyone to check who’s where. Most changes are made here.
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.