+ Reply to Thread
Results 1 to 4 of 4

Creating a Rolling Rota, need to change name dependant on date value

  1. #1
    Registered User
    Join Date
    11-25-2016
    Location
    Warrington
    MS-Off Ver
    2010
    Posts
    60

    Creating a Rolling Rota, need to change name dependant on date value

    Hi,

    Attached below is my form, on the sheet named "SEO shifts" I have a rota, on the rota the times and content remains the same but the name changes dependant on the date.

    I've currently made a static list for the following weeks but the issue I'm going to face is that all I have made change is the days, so the week after the same people will be doing the same thing, so its not going to make it fair.

    is there anyway that I can make the names roll every new week and then have the "current week" display what the person will be working this week?

    Once you see the form I am hoping it will make sense?
    Attached Files Attached Files
    Last edited by ahalliwell; 06-08-2017 at 05:27 AM. Reason: spelling error

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Creating a Rolling Rota, need to change name dependant on date value

    I'm not 100% sure it's making sense, but I think I get it? You want shifts to rotate, you need to be able to see what shifts people will have weeks in advance, you want that shift rotation to hold true on your workbook as the weeks advance, and you want it automated. I think my attachment does the trick? I turned your list of names in B91:B96 into a named range called "TheTeam", then used the following formula in B4:

    =INDEX(TheTeam,MOD(WEEKNUM($D$3,1)+ROW(1:1),COUNTA(TheTeam))+1)

    Fill it down, then modify it for B15:

    =INDEX(TheTeam,MOD(WEEKNUM($D$14,1)+ROW(1:1),COUNTA(TheTeam))+1)

    ...and fill down. Repeat for the other weeks. The names are tied to the week number of the date for that Monday, so as the days pass and the weeks eventually shift up, the names should stay true to the week and advance closer to the "current week" as well. I wasn't sure what to make of the blank space in "The Team", so I ignored it, leaving it with the bottom shift. If you end up needing a name there, just expand the named range to include the new name and fill the B4 formula down to B10 to incorporate the (now unassigned) extra row of shifts. Take a look at the attachment to see if it captures what you're after:


    Note: I reordered the names in B91:B96 so the returns in the current week would align with your sample's current week's assignments. This could have been done within the formula, but would have required adding an unnecessary extra element who's purpose would become unclear in a few weeks time.
    Attached Files Attached Files
    If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".

    If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
    https://www.excelforum.com/the-water...ke-thread.html

  3. #3
    Registered User
    Join Date
    11-25-2016
    Location
    Warrington
    MS-Off Ver
    2010
    Posts
    60

    Re: Creating a Rolling Rota, need to change name dependant on date value

    Superb!

    Thank you so much for your help!

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Creating a Rolling Rota, need to change name dependant on date value

    Glad to help, good luck!

+ 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. Creating a dynamic rota.....is this even possible?
    By Datagirl8472 in forum Excel General
    Replies: 15
    Last Post: 06-12-2016, 04:44 AM
  2. [SOLVED] finding average with date dependant and cell dependant
    By sfoll in forum Excel General
    Replies: 4
    Last Post: 08-24-2015, 04:58 AM
  3. Rolling Count Reset to Zero (dependant on txt not numbers)
    By Sabrina78 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-06-2012, 09:08 PM
  4. Replies: 0
    Last Post: 06-27-2012, 08:38 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. Rolling rota
    By rds2472 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-16-2009, 04:26 AM

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