How do I replicate the month so that I have a worksheet with 12 months instead of one? I want them to all to be the same size but to have them appear one after the other as I scroll down the page? I want to be able to view the upcoming months. Also at 9:15, how are the cells being frozen? What buttons are being pressed?
This was a super helpful tutorial. Thank you so much. Any suggestions on how to change formatting to a month-year drop down? So this can be used for past and future years (vs this year).
How do I get the years to update automatically? For example, I'm starting in Jan 2023 so my calendar view works through December. If I change the view to January, it goes back to 2023. I'd like it to go forward to 2024. Kindly advise me. Thnx
I have multiple events happening on the same date but it's only pulling in the first occurrence in the list, is there a way to include all events in the list in one cell?
i see this is 4 months ago but ..... he did cover this in the video, after each event in that same cell press 'Alt+Enter' this line breaks and adds next value on a different line but in the same cell, then on calendar page just 'Wraptext' the cell it is pulling into
Great video. If you had more than 1 event on the same day, but they aren't included in the same cell in your list of data, how would you make sure all events on that specific day are pulled into the calendar view?
Instead of making the row height bigger, try leaving like 3-5 rows untouched then move to the 4 or 6 row (depending on how many rows u need each day)and add the next week formula there. For instance if b4 was my first cell blank to add event , then type the formula there. Then just copy and paste as many times you need too. That is as far as I have learned to have birthday, anniversary,bills etc. After that edit the row height to desired height. Hope this helped
Great work 8:50 what if I have multiple events but rather than alt&enter on one cell, what would it be when adding additional same date events added at a later time?
In that case you would need an extra cell to accommodate that. I'll have an updated version of this calendar available soon that will make that easier to do.
Wait a minute!!! I see where you put Nov 11 in one time with an entry next to it, and a blank date, with an entry, but the calendar pull up them both. How the heck did that happen? When I put in the date two times, with corresponding info, the Match-Index pulled up one line. This confused me.
i tried the highlighting conditional formatting for football fixtures , but it only works on the 1st one i try, it will not work on multi dates, any ideas ..?
Never mind I figured it out , I had to make only 2 conditional formatting rules. 1 to search for dates >> =D4=VLOOKUP(D4,Dates!$A:$A,1,0) and the other to search for event >> =D4=VLOOKUP(D4,Dates!$B:$B,1,0) (I made a separate grid for each month :) was fun to figure that out :)
The simplest way would just be to make an entry for each day an event is on. If you deal with lots of ranges, then you could consider using two columns, a start and end date. And then you could check if the current date falls within that range (using greater than or less than operators) and if it does, to extract the value.
great tutorial. wondering what the solution is for a range of dates? if i put in a range of dates in my date column, i.e. a 3 day period, then the entry in the calendar disappears altogether. how to i amend this?
Thank you for this great tutorial, quick question...is there a way to view all months on one page if I want to print the complete year calendar so share with someone?
You can definitely fit in all the months on one page but then you're going to sacrifice a lot of detail; your months would have to be pretty small. So just imagine creating 12 months of this and just making the calendars much smaller in size. I've created a template like this a few years ago: howtoexcel.net/2019/11/excel-12-month-calendar-template.html
How do I get the years to update automatically? For example, I'm starting in May 2023 so my calendar view works through December. If I change the view to January, it goes back to 2023. I'd like it to go forward to 2024.
did you solve this ..? (isee this was 1 month ago) but i solved this myself :) i just added a downdrop box with years and in 'Start' cell i put formula =DATEVALUE("1/"&D1&"/"&I1)
@@scottjones1474 thank you. I cheated a bit. I copied the calendar to a new version and replaced (TODAY) with a cell valued with the actual year (2024). Worked like a charm!
Video is great, its simple but I have a problem. When I do the same thing like you I don get anything, can you suppose what is wrong because I don't know, tried to find but whit no luck.
I am stuck. When I copy the Start date formula across the top and change the month the only date that is working is the first date, everything else is blank, which causes the whole calendar to #Value...
I had to use the following to get the start date. =DATEVALUE(TEXT(MATCH(B3,{"January","February","March","April","May","June","July","August","September","October","November","December"},0),"0")&"/1/"&YEAR(TODAY()))
Question: how not to show the previous holidays in the caldendar once you select the next or current month, I don’t the formula in conditional formatting, I tried many times but it won’t run 🥹
This was a super helpful tutorial. Thank you so much. Any suggestions on how to change formatting to a month-year drop down? So this can be used for past and future years (vs this year).
