Move Calendar Events

Hello,

This workbook is thanks to Pete_UK that I am editing to better suit my needs. (Thank you Pete)

The Calendar sheet has Holidays and Important Events (by formulas) in the cells next to the days on the Calendar sheet. The problem is that the Important Events are the ones showing and the Holidays don't show.

Instead I would like the Holidays only to be next to the days and the Important Events to be listed in the area G41:H46 on the Calendar sheet showing the day and the event.

The formulas in the Calendar sheet next to the days also should be updated to not include the Important Events.

Thank you very much whenever someone has the chance to help.

BB

You can change the formula in B12 to this:

=IFERROR(VLOOKUP(DATE($N$40,$M$40,A12),'Todo-Holiday-Event'!$H$7:$I$26,2,FALSE),"")

Then you can copy this to all the other cells which are next to the days of the month. It will only display the holidays.

To get the list of Important Events (given that there might be a few of them in any given month), I first of all set up a helper column on the Events sheet, with this formula in N7 of that sheet:

=IF(TEXT(L7,"myyyy")=Calendar!$M$40&Calendar!$N$40,MAX(N$6:N6)+1,"-")

Copy this down as far as you need (e.g. to N26). Then you can use this formula in cell H41 of the Calendar sheet:

=IFERROR(INDEX('Todo-Holiday-Event'!M:M,MATCH(ROWS($1:1),'Todo-Holiday-Event'!N:N,0)),"")

which can be copied down to H47. I'm not sure what you wanted in column G of that sheet, as it isn't wide enough for the full date - perhaps just the day?

Hope this helps.

Pete

By the way, I like what you've done to this calendar file.

On re-reading your post, I see that you want the day in column G, so put this formula in G41:

=IFERROR(TEXT(INDEX('Todo-Holiday-Event'!L:L,MATCH(ROWS($1:1),'Todo-Holiday-Event'!N:N,0)),"d"),"")

then copy down.

Hope this helps.

Pete

Perfect thanks so much Pete everything works great!

Sorry I did see a small issue. On the Calendar sheet in the month of June Memorial Day shows in cell F40 but should not be. Can you take a look when you get a chance?

Thank you

F40 should be empty (as well as E40), as you will never need more than 2 days in that final (6th) week of any month, and this only occurs if a 31-day month starts on a Saturday. You just copied the formula too far.

Hope this helps.

Pete

Thanks again Pete I didn't notice that being too many days.

To explain why it happened, with E40 being empty the formula was effectively were looking up DATE(2021,6,0), which is interpreted by Excel as being the last day of the previous month, i.e. 31st May 2021.

Hope this helps.

Pete

Yes for sure that does help. I appreciate the education. I especially like the use of helper cells to make the formulas less complicated.

Cheers!