+ Reply to Thread
Results 1 to 16 of 16

Creating a dynamic rota.....is this even possible?

  1. #1
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Creating a dynamic rota.....is this even possible?

    Hello

    I'm wondering if I'm trying to do the impossible, or if any of you guys can help direct me?

    I have a worksheet that has Employee Name and a designated Shift (for example EmployeeA must work Monday am; Employee B must work Friday pm etc for a total of 70 employees). This worksheet also contains their phone numbers, working team allocations etc and is a really simple sheet where users can come in and make amendments to the teams or designated shifts etc.

    what I then need, is for excel to magically take this information and put it into a calendar for me - so another sheet, or workbook if that's better, I need the dates as columns, with a row for each employee, and where there is a specific shift for that employee (as determined by the first worksheet), transfer that information into the appropriate cell on the calendar sheet.

    Does that make any sense? Help!!

    Thanks so much x

  2. #2
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Creating a dynamic rota.....is this even possible?

    That sounds doable. Do you have an example workbook? Saves us guessing & guessing wrong

  3. #3
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Creating a dynamic rota.....is this even possible?

    Good thinking batman.......
    I'll upload one now.

  4. #4
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Creating a dynamic rota.....is this even possible?

    Right, I've added a mock spreadsheet (I think!). The first worksheet is an example of how the data will be held. The second worksheet is how (roughly) I want it to look. I've manually entered the data, but that's where I need the help to make it do it by itself!! I've still lots of to learn with excel - I'm kind of hitting the ground running a little bit with this!!

    Thanks so much x
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Creating a dynamic rota.....is this even possible?

    Blowing kisses already?? lol

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Creating a dynamic rota.....is this even possible?

    Your file is password protected, so we can't open it.

    Pete

  7. #7
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Creating a dynamic rota.....is this even possible?

    You know I said lots to learn.......
    Password hannah1310
    Clearly I'll now go and change the password on the proper one.
    Sorry....***must drink more coffee***

  8. #8
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Creating a dynamic rota.....is this even possible?

    It's password protected

  9. #9
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Creating a dynamic rota.....is this even possible?

    Yup realised that....sorry.....password hannah1310

  10. #10
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Creating a dynamic rota.....is this even possible?

    Where do the employees make the changes, is there any particular column on employee information sheet they update?

  11. #11
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Creating a dynamic rota.....is this even possible?

    Management would make changes to the front worksheet, i.e. Change employee1 from a Monday AM to a Thursday PM.
    Employees themselves would have limited access to the calendar (or a duplicate) to put other shift requests in.

    Thanks so much for the help - it's appreciated!!

  12. #12
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Creating a dynamic rota.....is this even possible?

    I've made a start, but it's getting late here & time for bed
    I'll have a look tomorrow & post a file for you to try

  13. #13
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Creating a dynamic rota.....is this even possible?

    Amazing thank you

  14. #14
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Creating a dynamic rota.....is this even possible?

    Try this & see if it's what you're after

    It only works up to Employee7 because that's all there is on the 4WeeklyOverallTemplate worksheet.
    There's no buttons to press as it's a worksheet change macro & will trigger when something changes in columns D, E or F.
    Adjust anything in columns D,E or F on EmployeeInformation worksheet and the macro will enter the new data on 4WeeklyOverallTemplate worksheet.
    The dropbox link is for you to get the file. I can't upload any attachments to this site & haven't been able to for months.
    To see the code right click EmployeeInformation worksheet tab & click view code
    Cheers

    https://www.dropbox.com/s/1842tyxiww...8472.xlsm?dl=0

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26
    Thank you so much - I'll look first thing after school/nursery run - it's 003" here. I've just had a look at the code and it looks like that may just do it. Thanks so much - will look and let yoh know


    Quote Originally Posted by Philb1 View Post
    Try this & see if it's what you're after

    It only works up to Employee7 because that's all there is on the 4WeeklyOverallTemplate worksheet.
    There's no buttons to press as it's a worksheet change macro & will trigger when something changes in columns D, E or F.
    Adjust anything in columns D,E or F on EmployeeInformation worksheet and the macro will enter the new data on 4WeeklyOverallTemplate worksheet.
    The dropbox link is for you to get the file. I can't upload any attachments to this site & haven't been able to for months.
    To see the code right click EmployeeInformation worksheet tab & click view code
    Cheers

    https://www.dropbox.com/s/1842tyxiww...8472.xlsm?dl=0

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    11-24-2013
    Location
    Lancashire, England
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Creating a dynamic rota.....is this even possible?

    I'm just looking at this now - I think what you've done may just work!!
    will keep playing and will let you know. Thank you so much!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 14
    Last Post: 05-23-2015, 06:26 AM
  2. Replies: 0
    Last Post: 04-07-2015, 11:39 PM
  3. Creating a rotating rota
    By Ultima in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-02-2014, 12:08 PM
  4. creating a staff rota for 24 hour shifts
    By RONJONES in forum Excel General
    Replies: 4
    Last Post: 09-06-2013, 12:02 PM
  5. Help in creating staff rota
    By Suecee in forum Excel General
    Replies: 5
    Last Post: 05-20-2012, 08:38 AM
  6. Creating a staff rota
    By daustin3 in forum Excel General
    Replies: 0
    Last Post: 05-16-2012, 12:30 PM
  7. Dynamic rota pattern
    By kt1978 in forum Excel General
    Replies: 7
    Last Post: 06-06-2011, 06:17 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1