+ Reply to Thread
Results 1 to 7 of 7

Generate Roster based on working days

  1. #1
    Registered User
    Join Date
    07-03-2020
    Location
    EST
    MS-Off Ver
    365 Pro
    Posts
    2

    Generate Roster based on working days

    Good Afternoon Everyone!

    My goal is simple, but being an excel noob is making this extremely difficult. I've been doing some research and have been trying to figure out how to get this to work, however I believe I'm going to need to use more than vlookup to accomplish my task.

    I'm trying to create a spreadsheet that for a role call for each day. I want each tab to populate based on employees working schedules since they can change somewhat often. I'm trying to use a master tab that has all employee info (Name, What days they work, and phone number). I'll posta dummy excel sheet below to provide some clairty. If someone could point me in the right direction, I would be extremely grateful. I've been doing this manually for the past few weeks and I want to learn to be proficient in excel in order to make my life easier. Appreciate any guidance you may be able to provide!
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    1,831

    Re: Generate Roster based on working days

    Try like this:

    Please Login or Register  to view this content.
    then run macro SplitShifts.

    works fine in attached file
    Attached Files Attached Files
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,152

    Re: Generate Roster based on working days

    Here is a formula based alternate to the VBA solution in post #2 above.

    The following is an example of what I think you are looking for (for the Saturday worksheet)

    stax90.png

    The formula in cell A2 copied down to A10 of the Saturday worksheet is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The formula in cell D2 copied down to D10 of the Saturday worksheet is a simple vlookup:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you have more than 9 employees then change all occurrences of "10" in the above formulas to something larger and copy the above formulas down as far as needed.

    For the worksheets for the other days of the week simply copy the Saturday sheet exactly as is to each of the other days. No other changes are necessary.

    The attached workbook implements the above (for Saturday, Wednesday and Friday)
    Attached Files Attached Files
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  4. #4
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,152

    Re: Generate Roster based on working days

    In addition to the two options provided above in post #2 and post #3, here's another thought:

    Can you not simply do everything you want from the "Main roster" worksheet and get rid of all the daily worksheets simply by a slight re-layout of your "Main roster" worksheet and then using Excel's auto-filter (on B3:H3) to show only those employees that are applicable to the particular day of interest? Much simpler, no VBA and no complex formulas.
    Like this:

    DAOberg_alternate.png

  5. #5
    Valued Forum Contributor
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    713

    Re: Generate Roster based on working days

    You are using 365 Pro - I know with O365 you get the new Dynamic Array Formulas so I'm assuming you get them w/ Pro as well.

    In any sheet, in cell A2 you can enter:
    =FILTER('Main Roster'!$A$2:$A$6,ISNUMBER(SEARCH(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),'Main Roster'!$B$2:$B$6)))

    In Cell D2 enter:
    =FILTER('Main Roster'!$C$2:$C$6,ISNUMBER(SEARCH(MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,255),'Main Roster'!$B$2:$B$6)))
    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  6. #6
    Registered User
    Join Date
    07-03-2020
    Location
    EST
    MS-Off Ver
    365 Pro
    Posts
    2

    Re: Generate Roster based on working days

    GeoffW283 Thanks so much for the help. Your formula solution was exactly what I was looking for. I have 0 experience with VBA, but I was able to figure out how the formula worked and tailored it to my specific needs. I appreciate your help sir!

  7. #7
    Valued Forum Contributor
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,152

    Re: Generate Roster based on working days

    Glad I could help. Thanks for the feedback and reputation points. I'll take the thanks but I'm curious though why you didn't go with Greg's solution in post #5. I don't have Office 365 but O-365 features like "filter()" can drastically simplify some problems (like this one) as Greg showed.

+ 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. Calculating continuous days of working in staff roster
    By sushil10s in forum Excel General
    Replies: 1
    Last Post: 01-16-2019, 05:02 PM
  2. Roster Days to Hours per Week?
    By Gazsim in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-17-2018, 10:32 AM
  3. Replies: 2
    Last Post: 06-20-2015, 10:40 PM
  4. [SOLVED] I need to return names from a work roster list based on their rostered days on/off
    By notthatgoodwithexcel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-31-2015, 09:50 AM
  5. A way to generate a rotating duties roster automatically
    By Adamus Prime in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-21-2013, 12:46 AM
  6. Replies: 1
    Last Post: 01-20-2011, 09:40 PM
  7. Generate random # of days based on other criteria
    By Georgia Golfer in forum Excel General
    Replies: 3
    Last Post: 09-17-2010, 02:00 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