Auto-populating calendar?
Thanks:  0
Likes:  0

1. ## 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. ## 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. ## 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. ## 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

#### Posting Permissions

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