I asked this in another part of the forums, and I should have posted it here since it will likely take custom functions, etc. Here was my question:

I want to create a standardized visual schedule for instructors to show what classes they teach, the time of these classes and so-on, and also show when their office hours will be. I plan to make a calendar on one worksheet with the days monday-friday in columns, and the rows be half-hour increments. I was thinking of having the instructor enter their class information on the first page with a column for 1) subject prefix (such as MAT, ENG, etc), 2) Course number, 3) Room number, 4) checkboxes for days of week, 5) start time 6) end time.

From this list, the second excel sheet will populate to create a schedule. For instance, if a class MAT 203 is entered that it on tuesday and thursday from 12:05 to 1:45, on the visual schedule page I would like for the 12:00 through 2:00 cells to be highlighted and notated with the course name, room and actual time (12:05-1:45). It would be great if it could state this information once over the two-hour block of time (instead of in each 30-minute cell), but I'll take what I can get!

I have a good idea how to set this up with a formula and fixed references, but it would be a REALLY long if/then formula checking for time and boolean value for day, and then inserting/concatenating the text to place in each cell. Any thoughts on how this could be done in a more abbreviated manner? A custom equation in VB? I guess that would be a cleaner way to do it. Any suggestions would be appreciated!