This project started out from a conversation I had with my Operations Manager at Goode Company Seafood where I work as a server(at the time of this post anyway). I found some discrepancies with my pay and explained to him that I could send him the relevant files to verify my findings. He asked, “You know Excel?” I said, “Yes, I do.” He went on to explain that he was trying to figure out how to take a list of employees and assign them to stations randomly, but couldn’t figure out how. So, I went home and built a much more simplified version of what you see here. I sent my Manager a copy and he was impressed. He was so impressed he started using it at my store. I wasn’t expecting this as what I’d built was essentially a proof of concept and not much to look at, so I started to build the application you see below. Being that I work as a server at this location I had unique insight as to the specific needs for the company. I used these insights to build an application suited to the specific needs of my employer.
A quick note, if you take a look at the VALIDATION TABLES sheet, you’ll have a better understanding of what you’re seeing as far as data validation going on on the BUILD AND VALIDATE SCHEDULE sheet as well as the SELECT SHIFT FOR FLOOR CHART sheet. Please feel free to change values and see how everything works. Be Shure to select a shift on BUILD AND VALIDATE SCHEDULE & SELECT SHIFT FOR FLOOR CHART as validation is handled on a shift-by-shift basis. There is a detailed description of the file below the spreadsheet if you require more insight or you can download a copy if you really want to dig into the code.
Purpose and Functionality
This workbook was developed for Goode Company Seafood to efficiently manage and automate the scheduling of servers. It handles the assignment of shifts, designates shift leads, and dynamically allocates servers to available stations based on various criteria. This application is currently in use at the establishment, streamlining their scheduling process.
Key Functions and Features
Scheduling Information Retrieval:
- The workbook retrieves detailed scheduling information, including which servers are working and their respective start times.
Shift Lead Designation:
- The system identifies the shift lead (SL) by appending “SL” to their start time. The SL is assigned a fixed value of 1 to ensure their priority in the ranking process.
Random Number Assignment:
- All other servers are assigned a random number between 0 and 1, which is used to rank them for station assignments. This ensures a fair and random distribution of stations among servers.
Dynamic Station Assignment:
- Servers are ranked by their random number, with the SL always being first due to their fixed value of 1. The system then checks this ranking against a dynamic array of closed stations.
- Servers are allocated to non-closed stations, ensuring optimal coverage and efficiency.
- The SL is assigned to a specific station determined by a reference cell value, which can be adjusted as needed.
Closed Stations Handling:
- The workbook includes functionality to dynamically identify and handle closed stations, ensuring that no servers are assigned to unavailable areas.
Validation Tables:
- Several tables in the workbook provide dynamic validation for scheduling and shift lead designation.
Advanced Functions
LAMBDA Functions:
- The workbook makes extensive use of LAMBDA functions to create custom, reusable formulas that simplify complex calculations and enhance the readability and maintainability of the code.
Dynamic Arrays:
- The use of dynamic arrays allows for efficient and flexible handling of data ranges, making the system adaptable to varying numbers of servers and station closures.
FILTER Function:
- Extensive use of the FILTER function allows the workbook to dynamically retrieve and display specific subsets of data based on defined criteria. This ensures that only relevant information is considered for each step of the scheduling process.
CHOOSECOLS Function:
- The CHOOSECOLS function is used to selectively extract specific columns from a range or array, enabling more precise data manipulation and reporting.
Conditional Logic and Error Handling:
- Advanced conditional logic is used to manage the assignment process, ensuring that all criteria are met and that the system adapts to any changes in real-time.
- Error handling ensures that any issues in data entry or system logic are caught and addressed, maintaining the integrity of the scheduling process.
Validation Tables
Server Approval for Shift Lead (SL):
- A dynamic array lists all servers and indicates if a server is approved as a SL through a dropdown list specifying “YES” or “NO”. This status can be updated via the dropdown, reflecting on the SCHEDULE sheet to validate that an approved SL is scheduled for each shift.
Server Requirements by Shift:
- A table allows for setting the number of servers required based on the day of the week and whether the shift is AM or PM. This is linked to the SCHEDULE sheet to confirm the correct number of servers are scheduled. These levels can be adjusted by modifying the corresponding values in this table.
Station Closure Designation:
- Another table designates which stations are closed based on the number of servers scheduled for the day. A station can be marked as closed by changing its value from 0 to 1, ensuring no servers are assigned to closed stations.
Additional Features
User-Friendly Interface:
- The workbook is designed with a user-friendly interface, making it easy for managers to input data, adjust settings, and view the final schedules.
Automation:
- By automating the scheduling process, the workbook reduces the time and effort required to manually assign shifts and stations, improving overall efficiency and accuracy.
Conclusion
The “GCSEAFOOD.xlsx” workbook is a comprehensive tool for managing server schedules and station assignments at Goode Company Seafood. By leveraging advanced Excel functions, including LAMBDA functions, dynamic arrays, FILTER, and CHOOSECOLS, along with extensive validation mechanisms, it provides a robust solution that ensures fair and efficient scheduling, adaptability to changing conditions, and ease of use for managers. This application is currently utilized at the restaurant, demonstrating its practical value and effectiveness in a real-world setting.