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

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,

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

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

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

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

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

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

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

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

apologies for the multiple posting - I was given an error message that said
my message hadn't been sent - I'll have to be more patient next time!

"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