Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Auto-populating calendar?

  1. #1
    New Member
    Join Date
    Mar 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Auto-populating calendar?

    Hello all,

    I am trying to build a calendar that, if the calendar date falls within the class dates, located to the right, it will display a value.

    I have the Calendar days easy and displaying the day of the week above them.
    I have the class start date and the predicted end date
    I need a formula that populates beneath the date, when we will be required to schedule an instructor
    Monday Tuesday Wednesday Thursday Friday
    02jan 03jan 04jan 05jan 06jan

    That pulls from this table, which states what dates the classes will actually be in attendance

    Class name Class module Start date end date
    #1 class module 1 01 jan 18 jan
    #2 class module 2 01 jan 05 jan

    End goal: I will know that Monday-Thursday I need two instructors a day, but on Friday, I only need one.

    Monday Tuesday Wednesday Thursday Friday
    02jan 03jan 04jan 05jan 06jan
    #1 #1 #1 #1 #1
    #2 #2 #2 #2 -

    Any help would be greatly appreciated, even if it's to say that it cannot be done; any suggestions for improvement are very welcome!

    -Shawn

  2. #2
    Board Regular
    Join Date
    Dec 2013
    Posts
    856
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-populating calendar?

    Yes it is possible .. but for my solution you will need to make a slight mod to your summary table ( insert column A and copy the class names into A as in my shot below)

    I also moved the list of classes and their start and end dates to another worksheet (I used sheet4 but you can use any sheetname you like , just modify my formula)

    formula in B3 =IF(AND(B$2>=VLOOKUP($A3,Sheet4!$A:$D,3,FALSE),B$2<=VLOOKUP($A3,Sheet4!$A:$D,4,FALSE)),$A3,"") .. Copy this formula across to columns C to F and down as many rows as needed

    Monday Tuesday Wednesday Thursday Friday
    Class name 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan
    #1 #1 #1 #1 #1 #1
    #2 #2 #2 #2 #2
    #3 #3 #3 #3 #3
    #4 #N/A #N/A #N/A #N/A #N/A

  3. #3
    New Member
    Join Date
    Mar 2017
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-populating calendar?

    Awesome thank you! I've been messing with IF/AND formulas when I needed to be checking out VLOOKUP. Appreciate the assist!

  4. #4
    Board Regular
    Join Date
    Dec 2013
    Posts
    856
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Auto-populating calendar?

    just one last thing ... remember you will need to add new classes to both the VLOOKUP sheet and column A of your summary sheet ; if you like you could also delete old courses from both

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com