none
Creating an excel employee schedule for multiple sites and for temp work that comes in with a large data pool RRS feed

  • Question

  • I need to create a fairly complicated excel format for scheduling, I have multiple permanent sites and receive last minute request for coverage. With a large employee pool, some locations require specific training and certificates and others just simple training. What I would like to do is as follows

    1) I have all ready created master schedules for permanent sites but would like to link it so if some one calls in sick or books off vacation time I can use a drop down menu and choose site> employee name >type of absence > date range > the list of employees meeting criteria that are available based off the other site. If their not available their name will not show up . as well as if employee quite or was terminated. With the rules in place it will automatically create open shifts on that schedule and display open shifts on the date range not only in schedule but on another sheet that will be labeled open shifts. These shifts I will need to make automatic list with employees matching that sites criteria. With a note made on that cell indicating changes.  

    2) Having a designated area that if I put a clients name> type of bill rate > bill rate> date range (Start date, Start time - End date - End time)> option of 4 hr, 6 hr ,8 hr or 12 hr shift> site address and notes and linking required documents> and creating a quote etc. It will automatically create a temp schedule based on the requirements i put in with a drop down menu for employees available on those days.

    3) for employees working on a temp site or picking up extra shifts when they have been chosen on the drop down menu and confirmed automatic updates will be made to an excel calendar (all ready created) and saving it a master copy for me and a copy for specific employees. It cant be one master copy for all employees  as they will be at different locations so each one will need their own. 

    4) all while keeping track of hrs and if employees are going into over time or their shift are booked to close together.

    I have approximately 16 perm sites with hrs ranging from 24 hrs to 400 hrs per work I have already created master schedules for these sites in separate work books

    I need to keep working schedules for the temp work

    I also need to create availability list on a day to day basis based on employees availability that they send to me

    Know whether or not I am booking a full timer part timer or a casual employee

    I know that their are scheduling systems that do all of this and we have one but I need one where my office can use it whether or not we are online or not. As well when this is completed this will be for a quick reference and creating a schedule for employees on a calendar style 

    I know this is a lot but I really need the help with this, or if their is a link that can help me create a rough outline and I can try to put it together 

    Tuesday, June 6, 2017 3:52 PM

All replies

  • Hi PatrickC1984,

    I can see that you had posted a whole requirement.

    overall , I think that you need to use user forms in Excel with VBA code to fulfill your requirement.

    User forms are a critical part of Microsoft Excel programming in that they provide a surface that is totally under your control with which you can interact with a user. This makes it possible to build more complex Excel applications. Forms can also be used to collect and verify information from a user before it's entered into a worksheet. They can also be used as part of an add-in to display options and control execution of a particular task.

    you can create an individual user form for your each requirement and perform your desire operations on data.

    it is quite complex or somethings are not possible to fulfill by user interface of Excel. so you have to use VBA code to achieve that requirements.

    you had asked for the links that help you to achieve your requirement.

    below are links you can visit.

    Work with User Form

    Object model (Excel VBA reference)

    you can try to study the object model and try to use it in code.

    object model contains the information regarding properties, methods , events regarding Excel application.

    you can try to develop code for your requirement and whenever you get any error or you stuck somewhere you can ask a question here.

    we will help you to solve that error.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, June 7, 2017 1:42 AM
    Moderator
  • Thank you I will start using the links and hopefully I can figure it out. If I have any questions I will repost

    Monday, June 12, 2017 9:00 PM
  • Hi PatrickC1984,

    this thread is still open.

    if your issue is solved now then please try to mark the answer so that we can close this thread.

    if you do not mark the answer then thread will remain open.

    if your issue is still exist then you can try to let us know about that.

    we will try to provide further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 27, 2017 9:36 AM
    Moderator