+ Reply to Thread
Results 1 to 8 of 8

Auto-Populate Work Schedule

  1. #1
    Registered User
    Join Date
    01-26-2019
    Location
    Sacramento, CA
    MS-Off Ver
    2013
    Posts
    4

    Question Auto-Populate Work Schedule

    Hello,

    My work uses an Excel template to create our daily schedules. It's separated into two parts: Top Half - Organized by Floor, Bottom Half - Organized by Staff Person

    This is filled out by one person, who completes the top schedule to make sure each floor is staffed, and then populates the bottom half themselves. This results in regular errors, which is totally understandable with 5+ floors and 25+ staff people.

    Schedule Top.jpg
    Schedule Bottom.jpg

    I want to find a way to auto-populate the bottom grid with whatever desk that particular staff person is assigned to. I have tried VLOOKUP and MATCH, but I'm honestly not super experienced with Excel.

    Thank you,

    Xander
    Attached Files Attached Files
    Last edited by MeasureX; 01-26-2019 at 07:16 PM.

  2. #2
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,355

    Re: Auto-Populate Work Schedule

    the photoshop will love to help you with editing pictures to the desired result. us excel nerds only get warmed up to help a lot more when the have an example sheet both formulas you mention can be a part of the solution, how to use them and which is better suited will fully depend on how the underlying data is structured.

    your picture looks already pretty empty.. so might be suitable for upload. But if you do make sure all sensetive information (names, addresses, stuff like that) is removed.

  3. #3
    Registered User
    Join Date
    01-26-2019
    Location
    Sacramento, CA
    MS-Off Ver
    2013
    Posts
    4

    Re: Auto-Populate Work Schedule

    Hell Roel,

    Thank you for your reply! I attached the Excel file to the main post. If there's anything else that can help, please let me know.

    Thank you,

    Xander

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

    Re: Auto-Populate Work Schedule

    What is the significance of the colours in your file?

    Pete

  5. #5
    Registered User
    Join Date
    01-26-2019
    Location
    Sacramento, CA
    MS-Off Ver
    2013
    Posts
    4
    Quote Originally Posted by Pete_UK View Post
    What is the significance of the colours in your file?

    Pete
    They differentiate between two different employee classifications. Not super important, I think it's a hold over from when things were more seperated.

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

    Re: Auto-Populate Work Schedule

    You can put this formula in C49:

    =IFERROR(LOOKUP("zzz",$A$7:INDEX($A$7:$A$39,MATCH($A49,C$7:C$39,0))),"")

    then copy down to C74. Then you can copy those formulae in C49:C74 across to column J.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    01-26-2019
    Location
    Sacramento, CA
    MS-Off Ver
    2013
    Posts
    4

    Re: Auto-Populate Work Schedule

    Quote Originally Posted by Pete_UK View Post
    You can put this formula in C49:

    =IFERROR(LOOKUP("zzz",$A$7:INDEX($A$7:$A$39,MATCH($A49,C$7:C$39,0))),"")

    then copy down to C74. Then you can copy those formulae in C49:C74 across to column J.

    Hope this helps.

    Pete
    Pete,

    I got back to work today and was just able to try it out. That is wonderful, thank you so much! Our scheduler is going to breath a huge sigh of relief, I have no doubt.

    Thank you,

    Xander

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

    Re: Auto-Populate Work Schedule

    You're welcome, Xander - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Rotating Shift Schedule-Auto Populate
    By Gigem2000 in forum Excel General
    Replies: 1
    Last Post: 10-29-2018, 10:07 PM
  2. Random Auto Populate Staff Schedule
    By capefigaro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-13-2016, 09:20 AM
  3. auto populate weekly schedule from list
    By RGL_86 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2016, 12:27 PM
  4. Replies: 5
    Last Post: 01-05-2016, 06:35 PM
  5. Nested Ifs Logical Help to auto populate a schedule
    By alienj19 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-18-2012, 12:28 PM
  6. Shift Schedule - 3 Shift Coverage - Auto Populate Roll-up Summary Schedule
    By chips1256 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-31-2012, 09:32 AM
  7. Auto Populate Training Schedule in Calendar
    By Wessmaster in forum Excel General
    Replies: 2
    Last Post: 02-06-2012, 12:40 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