Calendar with auto-populating events (multiple events in a single date)

Dear all,
I am trying to generate a calendar which should automatically add events matching a specific date.
Since a picture is worth a thousand words, here is an attachment.
Any words of wisdom?
Thanks in advance for your help!calendar.JPG

I admit I completely misunderstood the purpose of this forum then, as I came over here for help.
The picture that I attached is a snapshot of a workbook that I created manually, simulating the desired outcome.
I have spent countless hours trying to figure it out, mixing and matching Vlookup, MATCH and other functions, and I failed miserably.

My apologies: it was never my intention to "complain", just like it definitely was never my intention to have somebody else do my work for free.
I have limited Excel experience (I have a degree in Chemistry), and I was hoping that somebody could recommend a process (e.g. combining different functions). Finally, I certainly don't mind attaching a sample WB.
Any piece of advice would help.

Given that your upper table will probably grow as you add more data to it, I would suggest that you have that in a separate sheet from the calendar.

You can generate the dates quite easily, rather than type them in yourself, by putting this formula in cell C8:

=B8+1

and then copying this across to AF8. Then you need to decide if you want the dates to continue further across that row, or to have separate rows for each month. If the latter, then you can have this formula (in say B11):

=AF8+1

and in C11:

=B11+1

copied across to AC11, as there are always at least 28 days in February. To accommodate leap years, you can use this formula in AD11:

=IF(MONTH(AC11)=MONTH(AC11+1),AC11+1,"")

and then for the start of March, as you don't know exactly where February ends, you can have this formula in B14:

=MAX(11:11)+1

The rest of the calendar can then be built up by adding 1 onto the previous date. Everything is now controlled by the start date in B8 - change this to 1st January of another year and the rest of the calendar will follow automatically.

Hope this helps.

Pete

Originally Posted by Richard Buttrey

Hi,

A few points of clarification

1. How representative is the data in the C3:E5 range?
i.e. are all the dates in each of the X,Y,Z columns always chronological?

2. Are all dates in any column ALWAYS later than a date in any of the previous columns?

3. Is the C3:E5 fixed. This is clearly a representative example and may not represent the production workbook. Do you have more columns and/or rows and does whatever you have vary from time to time.

It may be possible to create a compound formula to return the results, alternatively and if it were me I'd use a macro. Is that an acceptable solution?

Dear Richard,
thank you so much for your follow-up.
I will try to answer your questions point-by-point:
1. All the dates in each of the X,Y,Z columns will always be dd.mm.yy
2. Unfortunately, to make things more complicated, the dates in one column will not always be later than a date in any of the previous columns.
3. The range is fixed, but there will be many more columns (e.g. Tests) and Rows (e.g. Projects).

Sadly, my experience with MACROs is very limited, but I am trying to learn
Thanks again for your help!
ZZ

Dear Richard,
My apologies for the confusion: English is not my first language and I am sorry if sometimes I can't explain things clearly.
You are absolutely correct: my goal here is to find a combination of functions to generate the row 9 values which are derived from the matrix.
Thanks again for your help,
ZZ

It would help if you explained what the letters A, B, C and X, Y, Z will actually represent in reality. If these are actually words (names of Projects and Tests), then I don't think it would look very good to join these together in one cell separated by a comma. It would be better to list them in their own cell underneath the appropriate date, so that you might have, say, five cells under each date so that you can accommodate up to 5 "events" for each day.

Obviously, the row for February dates (and for subsequent months) would be moved down to accommodate these 5 cells.

I think your upper table would be better laid out if you listed each date vertically, with an appropriate entry for Project and Test in other columns. That would make it much easier to auto-populate the Projects and Tests on the corresponding date.

I have submitted many such calendar files to the forum and my general approach is to have a list of events in one sheet and the calendar in another sheet. The calendar generally displays a single month, where the month and year can be selected via drop-downs. Up to 10 events can be displayed for each day. I can amend one of these files to suit your requirements if you like.

Hope this helps.

Pete

Dear Richard,
thank you so much for the Marco: it works perfectly!
I will try to understand the code so that I can apply it to the real dataset.
I can't thank you enough for taking the time to look into it!
I wish you a wonderful weekend!
Warmly,
ZZ