DOR,

Thanks so much for the detailed response. I'm following to the letter, but
somehow it's not working for me. I am emailing you a test sheet based on
your instructions below. Would you mind taking a look and letting me know
what I'm missing.

Thanks so much,

Scott

"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
>
>