+ Reply to Thread
Results 1 to 2 of 2

Process Rotation Schedule - Can't be the same person on each day

  1. #1
    Registered User
    Join Date
    04-29-2020
    Location
    Schenectady
    MS-Off Ver
    365
    Posts
    10

    Process Rotation Schedule - Can't be the same person on each day

    I'm creating a rotating list of names for multiple different tasks. The below formula works as it should but does not take into account the names next to it on other tasks for that day. On the same day I do not want the same people to be on duty. Is there a way to incorporate a Index/Match function into this so it will return a offset value that does not match the other names that are being used from their respective lists?

    Main rotation formula which works

    =OFFSET('Input Data (For Schedule Prep)'!$B$2,MOD((ROW(G3)+J4),COUNTA('Input Data (For Schedule Prep)'!B:B)-1),0)
    enter image description here

    Second Formula Attempt that works but is finicky/must allow iterations:
    =IFNA(IF(OR(C4=I4,B4=O4,B4=U4,B4=AG4,B4=AM4,B4=AS4),INDEX('Input Data (For Schedule Prep)'!$A$2:$A$20,MATCH(1,(OFFSET(C5,7,0)<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(OFFSET(C5,7,0)<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(B4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(I4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(O4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(U4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(AG4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(AM4<>'Input Data (For Schedule Prep)'!$A$2:$A$20)*(AS4<>'Input Data (For Schedule Prep)'!$A$2:$A$20),0)),B4),B4)

    Example.JPG
    Last edited by johncap; 06-30-2020 at 02:27 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Process Rotation Schedule - Can't be the same person on each day

    I suggest you to upload a sample file.
    Look at the yellow banner at the top of the page for further instructions.

+ 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. Rotation Schedule
    By uniks in forum Excel General
    Replies: 1
    Last Post: 03-16-2014, 08:58 PM
  2. Weekly Rotation Schedule
    By mattyp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2013, 05:16 AM
  3. Work Rotation schedule
    By schaubers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2013, 01:14 PM
  4. rotation schedule
    By sam in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2006, 07:45 PM

Tags for this Thread

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