Skip to main content

Roster

Getting Started

The roster tool is a smart-excel file that allows site teams to rapidly build rosters on site and manage them in an active document.

The roster is designed to easily be consumed by the admin team PowerQuery, which will auto-generate the required TRACK software “Schedule” Uploads as required by the team.

This provides a significant cost and time saving to the business for what is otherwise an extremely manual and tedious exercise. It all starts with completing the roster correctly.

Completing a Roster​

There are two ways to complete a roster:

  • manual text entry and macro
  • copy and paste.

Personnel Details​

To begin completing a roster, fill out the personnel details in the grey table.

  • The first 4 Columns (Crew, Roster, First Name, Surname) must be completed for the roster tool to work correctly. Ensure that none of these fields are left blank
  • The following columns can contain any information that is required by the team using the roster
    • Select the dropdown to select a column category, or type in your own
    • Hide/Show Columns H-Q as required to tidy up the document

Formatting​

  • The space to the left of the index “#” column can be highlighted and merged as required to group lists of names or crews.
  • No rows or columns in the grey table should be merged.
  • Colour formatting or comments are allowed throughout the document.

Copy+Paste Day Codes​

  • To copy and paste a roster, highlight the required Day Code from the legend in the top left corner.
  • On your keyboard, “ctrl+C” to copy the daycode.
  • Select the cell(s) that you would like to paste this day code and use “ctrl+V”

Macro Day Codes​

There is a macro in the document that will clear all colour coding and reassign colour codes as per the legend colour codes. This can be useful if manually typing in daycodes or migrating information from a historical roster tool document.

  • Select the cell(s) that you would like to add daycodes to, and type in the relevant daycode manually
  • Copy+Paste this daycode or drag the cell across to the relevant cells as required
  • Click the Colour button to automatically colour-code all daycodes in the spreadsheet
  • NOTE: The macro is case-sensitive, if the day code is “SD”, the following will not be colour coded: “Sd”, “sd”, “sD” etc.

Day Codes​

The Roster tool contains codes for assigning to individuals for each day, such as “DS” and “NS”, or “SD and “SN”.

These codes must be used according to what is provided in the legend. If you require additional Day Codes that are not present, please send a request to your relevant admin team with the required Day Code(s) to be added.

Legend​

The legend provides all the available Day Codes for each site or contract.

This legend can be changed by clicking the filter button, deselecting all contracts, and selecting the contract relevant to you.

If the Day Codes exist in the legend, then they will be picked up by admin correctly. If they are not present in the legend, get in touch with your relevant admin team before proceeding.

Calendar​

The calendar in the roster tool is auto-generated based on a field in the “DATA” tab.

If the roster is required for a future date, please click on the data tab, find the startdate of the calendar, and update this.

The months in the roster tool will need to be realigned to the dates manually each time this is changed.

Administration Setup​

Day Codes​

To add daycodes:

  • clear all filters in the legend,
  • Add each day code required to the list.
  • Colour the text in the filter column (column 1 of the table) light blue to keep it invisible.
  • Open up the PQ and in the Data tab, add the new daycodes to the mapping table for the roster

Migrating from a historical document​

When migrating from an old roster tool, ensure that:

  • The calendar matrix formatting between the documents are identical (That the date columns and rows line up. If they do not, you will have to manually row/by/row migrate the information
  • If they do line up, you can copy Ctrl-C the information on the old document and Paste Values (Ctrl+Alt+V → Values) into the new roster tool.
  • Click the “Colour Roster” Button to colour code the Day Codes accordingly.

Q/A​

Q: Can I just add conditional Formatting to colour code my roster?​

A: No. Conditional formatting is what’s known as “super-volatile”. This means that it recalculates and reevaluates the rules every single time a cell in the document changes. If you imagine this for a calendar year over 50 rows of personnel, and 10-20 day rules (NS/DS/etc), that’s approximately 365,000 calculations created every time you change a single rostered shift.

Quickly, this can lead to very slow and unresponsive roster files.

This roster tool uses a static formatting approach, which speeds up the document dramatically and allows for a quick and efficient roster tool.

Q: Can I just insert rows or columns into my roster?​

A: No. As the roster is consumed by the PQ, the location of the rows and columns is very explicit and must be maintained the same. If modifications are required to the amount of rows or columns in the document, a document change request would be required.