Calendar - vlookup based on dates

[Scott]

I have a calendar I use for training events. Each event is logged in a sheet
with a start date, description, etc. First sheet looks something like this:

Event Title Start Date Duration (days)
Orientation 11/7/05 1
Quality 11/10/05 2

I have another sheet that lists all dates (not just those with training
scheduled) and I have a vlookup formula that checks for date match and then
pulls over basic information. Currently looks something like this:

Date Event Title
11/6/05
11/7/05 Orientation
11/8/05
11/9/05
11/10/05 Quality
11/11/05
11/12/05

Everything is working fine so far except I want to figure out a way to show
the event on each day it is actually taking place. Right now all I have is
the start date. Example below is what I want to end up with (notice Quality
is shown with both the actual dates).

Date Event Title
11/6/05
11/7/05 Orientation
11/8/05
11/9/05
11/10/05 Quality
11/11/05 Quality
11/12/05

My current vlookup formula is as follows:

=IF(ISNA(VLOOKUP(A12,Schedule!$A$2:$J$9998,2,FALSE)),"",VLOOKUP(A12,Schedule!$A$2:$J$9998,2,FALSE))

Any ideas?

Thanks,

Scott

[DOR]

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

[Scott]

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

[DOR]

Scott,

Your test spreadsheet had the course/event and date columns swapped
from what you had in your original message. In your message above, the
layout showed the course name left of the date, so I assumed they were
in cols A and B respectively, although your formulas may have indicated
otherwise - I didn't examine them carefully. I am sending back your
spreadsheet corrected.

Declan

[DOR]

One issue that might be of interest is that the approach I recommended
will not work if an event extends across a weekend, without being held
on the weekend, e.g. a three-day event starts on Friday and ends on
Tuesday. Similarly, it does not work if an event spans a holiday. If
you want to handle those situations we need to make a small change.
Let me know if that is necessary.

Declan

[Scott]

Declan,

You are AWESOME! Thanks so much for all your help above and beyond what I
could have expected.

Sincerely,

Scott

"DOR" wrote:

> One issue that might be of interest is that the approach I recommended
> will not work if an event extends across a weekend, without being held
> on the weekend, e.g. a three-day event starts on Friday and ends on
> Tuesday. Similarly, it does not work if an event spans a holiday. If
> you want to handle those situations we need to make a small change.
> Let me know if that is necessary.
>
> Declan
>
>