Error checking on existing roster

[mml]

I was hoping to share some ideas that I had to add error checking to improve a roster .

I was looking at my spread sheet and have identified the main issues. I don't want to reinvent the sheet but want to put some error checking into the process

I would like to see :



1. Not to allow any names to be added to the spreadsheet apart from those identified in column B , specifically B53 to B123 . If the name is not on this list it doesn't get added to the roster . Also if it doesn't match the spelling it doesn't get added to the roster . This would eliminate the problem of adding as an example .."Jenny" for "Jennifer ", its the same person but slightly different . Error checking vba or conditional formatting ???



2. Before the roster is even started there is a sheet 2 where all staff that are not available for the roster due to Annual leave , sick leave long service leave or just not a rostered day because they dont work a full week . I saw something similar last week at this site https://chandoo.org/forum/threads/data-reflecting-only-available-personnel.44801/

I was thinking that this is the info that they have on hand and if this could be done for the week then command button activated to exclude them i.e mark not available for roster in names B53 : B123



If it were possible to get these two things in that would stop the roster being incorrect every week . At the moment it has no error checking just straight up chair number allocation returning a name .

For the most part the sheet works but I would like to reduce the "stupid"factor . As I have said before hours spent creating the roster and its ALWAYS wrong.



Have ideas sadly no skills

PS I post this with some trepidation because I had posted on Mr Excel BUT the request is slightly different and I am not able to load the sheet for gurus to see and proved feedback. Hoping that this passes muster :banghead:

[SamT]

Karen, I have spent a couple of hours trying to comprehend your Spreadsheet, then a couple of hours letting it float in the back of my mind.

IMHO, you have reached the limits of Excel and it is time you considered using a VBA based system.

[mml]

Hello Sam T
Thank you for taking the time to review . A couple of hours is more than I expected and appreciate your efforts . Also thank you for confirming what I had suspected all along .
Soooooo having said that can you provide any wisdom on how to go about this . I would like to be an active participant in the process so that I can learn ... skills not great but am willing

Any guidance on what to read or how to cut it into chunks based on the issues . I am sure that there are many out there who encounter the same problem with staffing rosters

[SamT]

Just letting you know that I haven't forgotten you.

[mml]

Good morning SamT and others
I have spent much of yesterday looking for a solution that could be adapted to meet my needs . Searched under "staff roster", "roster"staff availability " , attendance" etc most have complicated convoluted calculations on shifts and $ calculations .

At this stage throwing ideas up to see what could be achieved
Thought there may be something at Online PC learning https://www.youtube.com/watch?v=NZ85zJJH360&t=256s with job allocator . Then found https://www.youtube.com/watch?v=gu2PRtl-mzc&list=PLk89qwxaseCEYvVQRaPsxJArsuEGVH8cN&index=7&t=907s.

Looking at it I think that to get over the issue would be a "list " which could be updated weekly to manage staff availability in those staff out based on the week commencing the availability by day ( am and pm ) and the group then reference to User for form split into groups rooms 41 to 44 ,10 to 12 and so on.

Ideas mainly for design as I have no vba talent and could be completely and totally wrong .Also not sure what could be achieved in VBA . Many sites have complicated rosters , simple is always preferable.
Also guessing that there will be others out there that would benefit from this project. I appreciate that this may not be a simple request

As always thank you for your time and any suggestions