Hi DOR,
I came across your discussion when looking for something similar.
I've got this to work, which is really great, however I'm looking for the
slightly more complicated version...I'm looking for something that CAN
accommodate overlapping events. I'm really not sure where to start with this,
but I would say that I wouldn't imagine there being more than 5 events
overlapping on any given day.
Any help/suggestions would be greatly appreciated!
Thanks


"DOR" wrote:

Scott,

Your formulas don't seem to be consistent with your layout,since you
use VLOOKUP with the data column to the left of the criterion column in
your lookup table (Schedule). Nevertheless, you can achieve what you
want as follows:

Assuming your Schedule sheet has the date in column A, the Course name
in B anf the duration in C, and your dates are in column A in your
calendar sheet with the course name in column B. We also have to
assume that courses do not overlap in time, since your calendar cannot
accommodate overlapping courses.

Enter the following in columns B to D on your calendar sheet:

B2: =IF(ISNUMBER(C2),INDEX(Schedule!A:A,C2),IF(A2<=D1, B1,""))
C2: =MATCH(A2,Schedule!B:B,0)
D2: =IF(ISNUMBER(C2),INDEX(Schedule!C:C,C2)+A2-1,IF(D1<A2,"",D1))
D1: any negative number, or blank.

Copy/drag the formulas in row 2 down as far as necessary.

As you can see, columns C and D are helper columns; D contains the end
date of the course, but don't put that in D1! You should probably hide
columns C and D, particularly C.

You should also be aware that these formulas are a little crude in that
they refer directly to the row above them, which can cause problems if
you ever insert a row in the range, but I am assuming you won't need to
do that. There are techniques to make the formulas impervious to
insertions but I don't think you need to use them.

HTH