Excel Roster – How to Create a Staff Roster
- Excel Roster – How to Create a Staff Roster
- VBA Roster tutorial series to be added soon.
- I’m going to show you how to make this fantastic excel roster template application step by step.
- It contains no code,is not macro enabled.
- Excel Roster – Staff Roster template. Video 1
- Excel Roster – Roster template. Video2
- Excel Roster – Roster template. Video3
- Excel Roster – Staff Roster template. Video4
- Let’s go through some the excel roster template features.
- I will show you some more of the Excel roster template features.
- Excel roster template: Formulas for Roster Sheet
- Excel Roster template: Formulas for List Sheet
Free Microsoft Office Tutorials
♠ ♠ Problems with rostering staff??
Is there a free solution? YES
Have a look at this…………….
VBA Roster tutorial series to be added soon.
I’m going to show you how to make this fantastic excel roster template application step by step.
Some years back the manager of workforce planning in the large organisation that I work for came to me, I guess because they had heard that I was an Excel evangelist and said “Trevor can you create us roster and daily workflow sheet generator?” I have to admit that I was a little nervous but from that point in time my love for creating rosters, roster analysis programs and workflow systems continue to grow. And I must say I’ve had a great time over the years making roster applications that are still working effectively today. So I guess you’ve picked up from that little bit of a preamble that when I start talking about rosters I get a bit excited. I really do.
What I’ve done here in this office tutorial is taken a lot of the principles and design features for a roster template application, taken out a lot of the hard stuff and put together this fantastic roster program that I believe you can create if you follow the steps in the four videos that I’ve put together. It’s been great fun making this application and I hope you enjoy the project and really benefit from this information. But most of all I hope you can enjoy the thrill of seeing something that you have created work for you and for others.
There has been a lot of interest in this application since I added it in late 2012.
It contains no code,is not macro enabled.
I have used here many features from many of the roster applications I have created over the years. If you have a basic understanding of Microsoft Excel you will be able to create this Excel roster if you follow my simple instructions.
Excel Roster – Staff Roster template. Video 1
Excel Roster – Roster template. Video2
Excel Roster – Roster template. Video3
Excel Roster – Staff Roster template. Video4
Let’s go through some the excel roster template features.
You will be able to adjust this to suit any need that you have. I will show you a 24 hour 365 day roster.This is totally variable
In this office tutorial I have set the roster template application to show you 13 periods of 28 day rosters.
The interface will take you to any roster period.
The dates for all of the rosters are set by entering a start date on the interface.
The sheet names are shown on the interface page. If you change the sheet names it will not affect the navigation. It is not necessary to show the tabs.
All of the variables are set from a sheet called lists. In this instance I have used Codes, you can use Times if you wish it will not affect the program.On this sheet we set the shift type the code and the description. Also staff names and other needed information are added here.
I will show you some more of the Excel roster template features.
The dates are set from the interface page by simply adding one day all rosters are populated.
You can return to the interface page by clicking the button named interface when we add staff or of the additional information about that staff member will automatically be added for you.
When we add a shift, we first select the shift type then the drop-down boxes will automatically only contain that type of shift. As you do this the count for each staff member is added automatically on the right, and the count for each day for all staff is added at the bottom.
I’m going to show you how to do this in sections. I will provide you with the formulas that I use. You will be able to pause your video work on your document and then return to viewing when you have completed that particular section.
Here are a list of the formulas that I used in this project
Excel roster template: Formulas for Roster Sheet
Sheet Name =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)
Please note if you are using a version prior to 2010 you will need to change the references to named ranges.Lists!$F$6 and Lists!$E$7:$E$44 will need to be named ranges.This applies to the data validation formula.
Here is addition information to help
If you are using Office 2003 0r Office 2007 then create 2 named ranges
1. Lists!$F$6 call it Cascade
2.,Lists!$E$7:$E$44 call it Category_Full ( dynamic range here is better but not necessary)
And all will be roses
Named range tutorials
Filter Validation =OFFSET(Lists!$F$6,MATCH($E$7,Lists!$E$7:$E$44,0),0,
Excel Roster template: Formulas for List Sheet
Vlookup Staff Details =if(isna(vlookup(value,range,col,false)),"",vlookup(value,range,col,false))
Other helpful office tutorials that apply to this project. AOTraining/Excel
Please offer your comments and suggestions on this roster template application.
I hope you have enjoyed this office tutorial. Please leave your comments below.