mml
08-17-2020, 04:48 PM
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:
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: