+ Reply to Thread
Results 1 to 2 of 2

Creating an individual schedule based off of two larger schedules

  1. #1
    Registered User
    Join Date
    02-06-2018
    Location
    shanghai, china
    MS-Off Ver
    2010
    Posts
    21

    Post Creating an individual schedule based off of two larger schedules

    Hi!

    (THIS IS MY FIRST FORUM POST!)

    I have been making a schedule for school, we have two terms running concurrently with multiple disciplines, instructors, rooms, and lessons.

    I have 3 sheets that are important:

    T1T4-Actual Calendar with drop boxes that connect to the class list, including the room number, instructor, showing both terms at once so I can check any room or instructor conflict, with buttons two hide one term so I can export each schedule separately for students and staff
    C1C4-Class list where data will updated and changed
    Indiv Sched- (and I dont know what the best way to do this is) ideally I would like to be able to select an instructor and then have a formula check each day, each block (morning, afternoon, and evening) for their name in both terms so that they can have a schedule with only their classes. I cant figure out how to do this. If theres a smart or a long way I will take it, even if it means having a sheet for each instructor thats totally fine. Or copying a formula into each cell....anything that allows me to print off a 7 column/day calendar with the classes they teach from both terms in one document.


    I would rather not simply hide or dim the others because when you print the calendar 14 columns across it makes the font really small and I think people will be confused.


    It's a bit messy because I had a deadline while I was making it so as new data was being entered I didn't clean up the tables and conditional formatting as I would like.




    [
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: Creating an individual schedule based off of two larger schedules

    Hi There,

    Hopefully this should help get this started off at least, if not entirely point you in the right direction.

    The layout is a tad tricky for lookups as you have duplications in Column A, Lesson, Location etc, you also have duplications in the date row, and partial text match for the staff member name as A. Collar/L. Chen for example.

    Without altering the layout, the best thing I've come up with is this.....

    On the Indiv Sched Sheet....

    These must all be entered as an Array Formula. To do this, hold down left control and left shift together, while pressing enter, the formula should have {} around it in the input box when done correctly.

    In B5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    (Copy and paste to table section B5:H9)

    In B11:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    (Copy and paste to table section B11:H15)

    In B17:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    (Copy and paste to table section B17:H21)


    You would need to continue to follow this same logic all the way down the sheet, modifying the relevant range sections in the formulas as you go down. It's done in the morning/afternoon/evening sections each separately, to be able to tell the difference between location, lesson, instructor etc for each section. A match on column A otherwise will find multiple instances of all of these, this is not the only way around that problem, but it's one way of doing it.

    I attached your example with this in place for week 1, hope it helps.

    Regards,
    Attached Files Attached Files
    Last edited by D.Lovell; 02-06-2018 at 01:16 PM.

+ 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: 2
    Last Post: 09-16-2017, 12:08 PM
  2. Making Individual Schedules Based on Master Matrix
    By selemat in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 08-20-2017, 08:36 PM
  3. Replies: 1
    Last Post: 07-14-2015, 01:02 AM
  4. [SOLVED] Creating a Teacher Schedule With Lists of Students based on student schedules
    By ked2313 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-24-2015, 12:18 PM
  5. Pickleball tournament bracket - macro to create individual player schedules
    By dhhume in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2015, 12:36 PM
  6. Create Individual Schedule from Master Schedule
    By kscheller in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2014, 11:47 PM
  7. Master schedule to individual schedule
    By barrec in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-21-2011, 01:24 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