Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Employee Scheduler

    Hello all,

    I am trying to create a sheet that will auto schedule associates based on their ranking in productivity and their availability.

    (ie) .. if Associate #1 is ranked #1 in productivity and they are available on the #1 sales day I want them to be scheduled the max number of hours they are available w/ o exceeding the total number of hours they are allowed to be scheduled.

    I was thinking i could do this w/ if then else and statements but I believe xl has a limit on the number of nested if statements....

    Any and all thoughts on how to accomplish this undertaking very much appreciated!!

    Thanks in advance to all who join in the thought processes..... I believe this may be a great learn for a lot of us !!! haha

    I am attaching an excerpt of the sheet I am working on.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Perhaps I am obtuse:
    On the sheet what value(s) will you be enter and what cells do you want to calculate?

    Could you walk us through the manual process that typically happens and possibly give some examples of oddball examples that must also be determined.

    [BTW XL allows 7 nested if statements, but other methods can be used, depending on logic]
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sdckapr.... My apologies .... the cells starting with B31 through u36 will be filled in depending on the number of associates employed at that time. the cells that show data currently are manually entered by the creator. Rows 13 and 14 will have calculations placed in to determine those particular cells.

    As a manual process the creator of the schedule would first determine which day is the highest sales day then look at the associate list and chose the person with the highest sales @ hr. and then check the availability of the associate and if available plug in max amount of hours the associate is available up to the max as listed in cell B12. If the associate is not available, being off or meeting etc., or maxed hours used already, they would proceed to the next highest sales @ hr. associate available. This process would continue till the maximum amount of hours is used, cell c6. I have not encountered any oddball problems so far.
    I hope this clears up the process.
    Thank you for the questions and information on nested if's.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I still don't understand. What does the "blank" sheet look like? Which cells will be manually entered, which cells will have existing formulas, which cells do you need formulas for.

    For the cells that you need formulas for, could you walk me through the logic? If someone has manually entered some values that need to be calculated how were they calculated?

    I would suggest showing us the "start worksheet", and then shows what would be manually entered, then describe based on the manually entries what needs to be examined and the logic used for determining the other cells that need to be calculated.

    It may be perfectly clear to you, but I think we need a little "hand-holding" and being led through at least one example in detail.
    Steve

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The "blank" sheet "Associate Schedule for the week" is the same as rows 30 thru 36. I have put hours to work in for Monday thru Wed. Manager hours will not be counted in total hours available, cell D6
    These are the cells that will need the formulas to be filled in based on the criteria described above.
    The area "Hours Associates is Available" is put in manually by the scheduler ... this is data that the associates relate to the scheduler on a monthly basis.
    Data in rows 1-14 is brought in from another sheet and the calculations are set for them.
    Thanks again for your input.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I guess I am the one not being clear now.
    I would like to see a sample workbook with 3 sheets
    The first should have the blank template, before anyone manually fills in anything
    The 2nd should be the workbook with the manual entry
    The 3rd should be a workbook with the cells filled in (manually) for what you want the computer the calculate. Also the logic on how the computer is meant to fill in those cells. Walk us through the process in detail so that we can understand it.

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    sdckapr:

    Thanks for helping ....
    I am attaching the workbook as requested ... please let me know if you have any other questions.
    Attached Files Attached Files

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I don't see any difference between what you expect to be manual entry and what the computer should do...

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Steve,

    My Bad... rough morning.
    Lets try this.
    Thanks
    Attached Files Attached Files

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am very confused. The computer info you have filled in was primarily formulas that were in the blank but that the manual one deleted? Why are they deleted in the manually if you want the computer to add them later?

    The other difference in the computer version is that some of the manual information was deleted. What is the sense of adding things that the computer will delete?

    Let me try again.
    I would like to see a sample workbook with 3 sheets
    The first should have the blank template, before anyone manually fills in anything (and without any of the information you are asking about. You may want to highlight those cells that you want use to help with. Formulas, you currently are happy with, you can leave in)
    The 2nd should be the workbook with the manual entry. this is what in practive you expect in the future someone will enter manually.
    The 3rd should be a workbook with the cells you need help with filled in (manually for now) for what you want the computer to calculate. Also for each of those cells, what is the logic on how the computer is meant to fill in those cells. Walk us through the process in detail so that we can understand it.

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Steve,

    Oversight on my part in setting up the examples where the calcs were eliminated in the manual sheet. They should be there as they are shown in the computer and blank.

    My logic to fill in the schedule manually,
    Cells starting at E31 the logic is to look at the Manager's schedule and add associates based on the % of sales targeted for that day ( row 3) and utlizing the associates working from the highest productive(row 6) to the least without exceeding the max allotted hours per person (row 12) based on associate availibility from area in rows 20 to 27. Row 6 is used just to alert the scheduler as to the max number to schedule and running total remaining.

    In other words I am trying to schedule the highest productive associate on the highest expected sales day w/ out exceeding the number of total hours they should be scheduled, as calculated on row 11. When making the schedule we run in to the normal challenges, (ie) wanting a certain person to work a certain day and they can not because they are off and we then need to work our way down the list.

    Your thoughts or questions. Hope this makes things clearer.

    Thanks again for your support and help

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Details please. you are asking for detailed formulas and/or code. I don't need to just understand the logic in general, but the specifics. You need to put forth the effort in providing what I need to help you, if want my help. So I ask a third time:
    I would like to see a sample workbook with 3 sheets
    The first should have the blank template, before anyone manually fills in anything (and without any of the information you are asking about. You may want to highlight those cells that you want use to help with. Formulas, you currently are happy with, you can leave in)
    The 2nd should be the workbook with the manual entry. this is what in practive you expect in the future someone will enter manually.
    The 3rd should be a workbook with the cells you need help with filled in (manually for now) for what you want the computer to calculate. Also for each of those cells, what is the logic on how the computer is meant to fill in those cells. Walk us through the process in detail so that we can understand it.

    I want a detailed walkthrough for at least 1 example...
    Steve

  13. #13
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    scheduler

    Steve,

    The first should have the blank template, before anyone manually fills in anything (and without any of the information you are asking about. You may want to highlight those cells that you want use to help with. Formulas, you currently are happy with, you can leave in) --- blank sheet ---cells that I am looking to have the computer fill in are highlighted

    The 2nd should be the workbook with the manual entry. this is what in practive you expect in the future someone will enter manually. .... manual sheet

    The 3rd should be a workbook with the cells you need help with filled in (manually for now) for what you want the computer to calculate. Also for each of those cells, what is the logic on how the computer is meant to fill in those cells. ... computer fill in

    Also for each of those cells, what is the logic on how the computer is meant to fill in those cells. Walk us through the process in detail so that we can understand it. .... the thought to each cell is to first look at the "sales area"(row3), and and evaluate which day is listed to deliver the most sales and then look to the associate availability (cells, rows 20-25), and schedule the highest productive associate (if available that day) to help augment the staffing for that day.
    If the highest productive associate is not available the process continues to the next highest productive and available associate.
    There must be at least one associate scheduled to open and one to close the store, based on the hours the store is listed to be open.(column A)
    The scheduler needs to continue monitoring and not exceed, both the max number of hours an associate should be scheduled,(row 14), and, the total amount of hours available for the store.(cell G6)
    That is it
    Thank you again for sticking with it...
    Attached Files Attached Files

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What you have attached here, is essentially what you provided before and does not give me what I need to proceed. This will be my last reply until I get what I asked for. I will try and be more explicit.

    Start with a workbook with 1 sheet: the "Blank Template". Thhis should be the start before anyone manually fills in anything (and without any of the information you are asking about). You may want to highlight those cells that you want us to help with. Formulas, you currently are happy with, you can leave in)

    After you have that template, copy that sheet within the workbook. Now in that newly created 2nd sheet, manual add and fill in the cells that you expect people to manually add when the process is working.

    When that is completed, make a copy of that 2nd sheet with the manual entries. In this newly created 3rd sheet (a copy of the 2nd sheet), manually add the information you want the computer to calculate and fill in.

    In your reply (or add it as a 4th sheet if desired) for those cells you fill in on the 3rd sheet, walk me through the logic for getting those numbers you want to be calculated. Be Explicit! Showe the logic using the example values that were entered in sheet2.

    Remember:
    Sheet1 = Blank Template
    Sheet2 = Sheet1 + Manual Entry
    Sheet3 = Sheet1 + Manual Entry + Computer Answers desired

    Sheet3 should NOT have less info than sheet2 (unless there is some logic I am missing that you have not explained about why the computer would delete manually entered information...)

    Steve


    The 3rd should be a workbook with the cells you need help with filled in (manually for now) for what you want the computer to calculate. Also for each of those cells, what is the logic on how the computer is meant to fill in those cells. Walk us through the process in detail so that we can understand it.

    I want a detailed walkthrough for at least 1 example...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •