Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel questions. (2K)

    Hi,
    I'm building a rota in excel and trying to see how much of it I can automate.
    1) I have a list of persons initials that are entered into a cell. One per cell. Thoughout a weekly basis. I'm using data validation to grab the data for the cell.
    So say in column A & C (which constitutes a AM & PM of Monday, E & G is AM & PM for Tuesday, and so on) I have a list of initials. Can I select what initials have not been chosen in these columns and concenate them into a single cell called Office? Unless as 2)
    2) I'd also like to concenate Annual Leave data, so the person taking A/L and the reason stated (if any)? Same applies for Other.
    3) And finally, certain people have requested to be certain places on certain days. So FH at GYMC4 on Monday's, in office on Tuesdays.... Is there anyway to throw up a confirmation message if any of these requests are breached?

    Sorry for so many questions, but I'm unsure of how to approach it.

    I've attached a copy of the spreadsheet which helps with my explanation.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel questions. (2K)

    Can you explain the meaning of columns X, Y and Z in detail?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel questions. (2K)

    X is the list of employees Initials.
    Y is a list of possible reasons for absense. Flex is flexi leave(hours accrued). Special is special leave (ie funerals, etc).Study is study leave(for doing course work),Appt is appointment, Mtg is meeting, Conf is conference, A/L is annual leave.
    Z relates to cover type. So Day 1-5 is court trials, Yth is youth Court, DCW is Designated Caseworker court.

    This is the data I use for data validation.
    X relates to B3:B15, D315,F3:F15 and so on. As well as left hand columns in the cells titled A/L, Other & Office.
    Y relates to the right hand columns of A/L and Other.
    Z relates to C3:C15, E3:E15, G3:G15 and so on.

    Hope this is enough detail. I've also attached an amended version as I thought I had already configured the data validation to the above (although apparently not). I must apologise for this.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel questions. (2K)

    Still confused about your 3rd question. Where in the spreadsheet, for example, can I find that FH hast requested to be in GYMC4 on Mondays, in office on Tuesdays? Do I need a crystal ball?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel questions. (2K)

    Yes, you are right, a crystal ball might be quite helpful.............. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>. Again apologies.
    The criteria would be:
    FH - Monday at GYPS
    DJS - Tuesday at GYPS
    SP - CMC1 Wednesday
    MI - CMC2 Wednesday
    CRS - GYPS Wednesday
    GM - Office Wednesday
    MI - GYPS Thursday
    FH - CMC 1 or CMC2 Thursday
    RO - CMC 1 or CMC2 Thursday
    RB - Office Thursday
    EH - PM only Thursday
    CRS - AM only Friday
    MI - Office or GYMCF/T3 or GYMCF/T3 Friday.

    I wasn't too sure how to enter this onto excel as I suspect a formula of some sort wil be needed.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel questions. (2K)

    This could become quite complicated - I suspect that once you set this up, "they" will want the ability to change the "rules" on the fly, and to make exceptions to the rules, and exceptions to the exceptions, and combinations, etc. etc. (if A is in the office, then B should be at GYPS, but not if it's the 3rd Thursday of the month, except when the moon is full or when C is on vacation)

    Instead of reinventing the wheel, I'd look at existing rostering software - do a Google search for staff rostering or something similar.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel questions. (2K)

    I did wonder whether the implications behind it were going to get somewhat complex.
    I was hoping that I could simply have a list (similar to the data verification) whereby the coding could refer to this and sort it out.
    I presume that this would be possible were I to ignore the specific requests for specific locations?

    I shall peruse google for some pre-prepared software.

    Thanks for your help

Posting Permissions

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