So I’m 76 and have worked using Excel in my former work place. Since retiring and rather than sitting on the Couch I took up learning VBA. Found some channels but most presenters rush along with the instruction. But am so happy to find your channel as you are Cristal Clear when speaking and I found I could keep up with you. Pleased to say developed the Event Calendar which I love. Bonus I now know how to make a Copy after your instruction. My sincere Thanks Ryan and of course I have Subscribed and Liked so will staying with your channel and checking out previous videos. 👍👏
Thank you so much for your kind words and feedback. It's incredible to hear that my work is so appreciated. Thank you. You really made my day. 😊 I'll do my very best to keep up the good work! 👍
OMG! That was the BEST excel training video EVER! Thank you so much for teaching us this calendar! I feel so empowered by this training! Thank you Thank you!
@ I just wish you could see my beautiful spreadsheet I made following both part 1&2. 🤩. Your videos was just what I was looking for! It looks sooo professional and it goes into the future with all the years! I made a ‘leave’ calendar and changed colors for the events adding in rules once they were on the calendar. It came out so nice! I’m just impressed that you know all those hard formulas!
That's so amazing! 😄 If you'd really like to share, you are welcome to send me an email at ryan@spreadsheetlife.com. Attach your spreadsheet, and I will see it. I'd love to see how it all turned out! 😊
THANKS SO MUCH!!! These tutorials were AWESOME. I'm trying to help a friend who isn't very tech savvy make an updatable events calendar for a nursing home. This is exactly what we need. I do need to figure out how to make each month a separate page and "pretty" it up so she can print it, but this gives me what I need to do it. I would also love to know how to make events not in the current month either not show up or show up in a different color.
I'm so glad that you have gotten so much out of these tutorials. If you are still struggling to figure out those last details, send me an email at info@spreadsheetlife.com, and I may be able to help you figure out those last few things.
I just wanna say thank you this worked out so very well! I had a couple issues but only mine. I didn't realize the table name was so important but I fixed it and now it's working really well thanks again 🎉
Thanks so much for your easy to follow tutorial. I was hoping to add a little something to mine. What can I do to have a place to enter my events at the top of the table ?
I'm so glad you enjoyed the tutorial! 😊 For your question, could you clarify a little bit for me? I apologize that I don't quite understand what you're asking.
Thank you for this amazing tutorial! Can you show us how to make the events recurring so that I don't have to put the date in manually for every event for every year pretty please?
Thank you so much for watching! 😊 For your question - I'm so sorry this is the case, but this design doesn't have an easy way to incorporate recurring events. The only way to do it is manually like you described.
This is wonderful tutorial and a very helpful sheet you helped create. BIG Thank You. Only thing missing and I would be great to have, recurring event if there is event every week/biweekly or monthly how to have it once and can show up in calendar
Of course! Thank you for watching! And that is a great question. So there isn't really a way to directly do that, but I do have some suggestions for you. One thing you could do is place internal events in one of the first two or three event spots and external events in the last two or three. Then change the font color for those respective rows on the calendar page. Another thing you can do is add conditional formatting to the calendar to color events that contain a certain word, character, or phrase a certain color. This would be more difficult to implement though.
Thanks for your tutorial about dynamic calendar i followed and did it, can you also show how to make appointment schedule for patients where i can see the details next to the calendar and it will change if i click on the specific day on the calendar.I still want the calendar plain as it is. I hope you notice, thank you very much
That's awesome! I'm glad you were able to follow and complete it. As for your question, this sounds like an slightly more involved question. If you'd like, send me an email at info@spreadsheetlife.com, and we can discuss the details there. It's a little easier than chatting through the comments.
Loved this two part explanation. I was wondering if it would be possible to do the opposite? Essentially, to fill in data in the calendar and have that auto populate a list. Also, have each month retain the information when toggling fro month to month. Is this possible?
I'm so glad that you enjoyed the part two explanation! 😄 For your question - so it is possible to add the data to the calendar itself and then have it show up in a list...but...you won't be able to toggle the month to month and have that information stick. So that's the down side unfortunately.
These two tutorials were great and very easy to follow. I do have a question though is there a way to make events recurring, i.e., weekly monthly, etc, and even annually, so birthdays come each year?. Also for adding events that go more than one day would be useful.
I'm so glad that you enjoyed these tutorials! 😊 For your questions, I've had a few other people ask me this, so they are great questions. Unfortunately, with this specific design, there really isn't an easy way to add recurring events or events that span multiple days. It's just not something I thought of when I put it together. I'm so sorry that this is the case.
Fantastic tutorial! In my situation, we organize our list a bit differently. Is there a way to bypass a date that returns a 'null' result, & check further down the column for another occurrence? Thanks!
@@spreadsheetlife Absolutely. We have an annual training calendar that regularly has multiple events on a single day. Many of the events reoccur on differing schedules (Annual, Quarterly, Monthly) or are one time occurrences. Rather than try to keep track of everything that happens on one row, it is much easier to assign a row to each event, and attach the date to that row. This results in having multiple rows with the same date. Unfortunately, VLookup stops searching down the column when it finds a match. What I have done for now is to have each category on its own table/sheet, and modified the formula so that each row on the calendar draws from a different table. It would simplify the workbook if there was a way to have a formula that found all occurrences of a date and pulled that data into the dynamic calendar.
Ok, I gotcha. Let's see, my first instinct is to suggest using the FILTER function. To better explain how to utilize the FILTER function, it would be quite a bit easier to discuss via email rather than in comments. You can email me at info@spreadsheetlife.com.
Thank you so much. It is very helpful. I want to add business days under each calendar date. Also holidays should not be considered as business day. Please help with the formula
Let me see if I understand your question. Are you asking how to enter text values in the cells below the date value that indicate if the day is a business day or not? Or is it something different that you are asking?
Thank you so much! I'm so glad that you enjoyed the tutorial. If you followed along and completed the tutorial, then the calendar will automatically update whether you change the month or the year. So no changes are needed. 👍
Unfortunately with this design, the only way to add an event that spans multiple days is by manually entering that event for each individual day. I'm sorry that's the case.
new subscriber here. love the content! I have a question though with this dynamic calendar, what if say for example I put on one of the events on a date the word "all activities cancelled due to holidays" or just the word "cancelled". i need the whole block to turn light red(including the cells that already has activities on it . what would be the best formating formula for that?😊
Thank you so much for subscribing!! I'm so glad to hear that you enjoy the content so much! 😄 As for your question - this is a tough one. My best advise for you is to send me an email at info@spreadsheetlife.com with a copy of your file. I will give it my best try to figure it out. No promises though. This one sounds tough like I said. But I can try.
That might be a stretch for what this template is capable of. Your are welcome to give it a try, but it might work out. It's hard to say without some more context.
I've done this and now on my events sheet (I've extended the dates to include all of next year) there are a bunch that are showing up as ################ How do I fix this?
This error could mean one of two things. 1 - The column is not large enough to display the date. To fix, expand the column width. 2 - You have negative date values. To fix, ensure that there are no negative date values.
I'm so glad you enjoyed the video! Unfortunately with this design, the only way to show multi-day events would be to manually enter the event for each day. I'm sorry that's the case.
So the way Excel works, you can't type the events directly into the calendar and also have them update whenever you change the month. I'm sorry that is the case.
Yes, this is possible. But it will require some extra conditional formatting rules. If you're interested in learning more, send me an email at info@spreadsheetlife.com.
Hi! I have been following the guide since Part 1 on Google Sheets. Now I have encountered and issue that the event dates does not go over to the calendar side. I have tried fixing it but am unable to. Could you explain why do we have to but B7 in the Vloopup? And after wards the number changes according to the row -1.
Let me see if I can help. So VLOOKUP references B7 because it wants to look at the date that's in that cell and use it as the lookup value. The VLOOKUP function then references the Events table and searches for that lookup value (from B7). Once it finds that date, it returns the corresponding value in the 2nd column (because we enter a 2). So that's what is going on. Now if that makes sense, and you still can't figure it out, send me an email at info@spreadsheetlife.com, and attach your file. I will take a look at it and see if I can help! 😊
Hello my friend. First of all, thank you very much for the video that you uploaded. Very helpful. As I see, I have to use (;) instead of (,), and I don't know why, but it's OK. But my real problem is that I have an issue when I'm trying to take the events. I don't know if I'm doing anything wrong or what. It returns this: #NAME?
This was great and very clear - thank you! Two questions: (1) Can I have an event that spans multiple dates or the entire month? (2) Can I pull through dates and event name from another sheet?
That so amazing to hear! Thank you for watching. As for your questions: 1 - Unfortunately not with this design. You'd have to enter in the event manually for each date. I'm sorry that is the case. 2 - Yes you can, but the formulas and specifics will depend on your specific situation.
Thank you sooooooooooooooo much for this great video, please next video if you can add that how we can make the event frequent monthly or Quarterly of Semi annual.
You're very welcome. Since I've already made four calendar videos (two for Excel and two for Google Sheets), I probably won't make another one for a while. But if I do, I'll be sure to include recurring tasks as you have described. I'm sorry it may be a while though.
Hi Ryan, Great videos, however, I'm not sure if I'm doing something wrong when I change month some of my January Events eg 30th January shows on the February, how do I stop this?
Hi there! So the calendar will always display the first Sunday before the 1st of the selected month. This is what you are seeing events from the previous month at the beginning. Now there is a way you can adjust the formulas so that the events don't show up. Would you be interested in learning that formula?
Okie dokie. So you would change the formula in cell B8 to the following: =IF(MONTH($J$5)MONTH(B7),"",IFNA(IF(VLOOKUP(B7,Events,2,FALSE)=0,"",VLOOKUP(B7,Events,2,FALSE)),"")) Then copy this formula down just like we did in the video, and be sure to update the references to B7 and change those VLOOKUP numbers to 3, 4, 5 etc. So your formula in cell B12 will end up like this: =IF(MONTH($J$5)MONTH(B7),"",IFNA(IF(VLOOKUP(B7,Events,6,FALSE)=0,"",VLOOKUP(B7,Events,6,FALSE)),"")) Then copy this formula over to the right for the whole row. Then copy, and paste for the rest of the rows in the calendar. I hope this helps. 😊👍
Awesome! I'm glad you got it to work. Yeah, including the MONTH function is definitely the tricky part in this case. You were on the right track with the IF function. Just needed that extra piece. 😊👍
Hello, thankyou for the tutorial! It really helped. Can you also also tell me what to do if the event date is like a period for eg: 02nd - 06th October 2024. Please reply
Hi there! I'm so glad that you enjoyed the tutorial. Unfortunately, there isn't an easy way to make an event span multiple days. Probably the best solution is to just re-enter the event for each date in falls on. So enter the same event for all four days.
Is there a conditional format formula to make the events that are not in the present month highlight in grey, similar to the dates that are not in the current month?
Hi, Thank you so much for this, it was really helpful. I’m having trouble to connect the events, when I add the IFNA formula it shows the #NAME? Error. I look and every formula seems to add up, so I don’t understand why it shows this. Any advice?
Hmmm...what version of Excel are you working in? If you are working with an older version of Excel, try substituting the IFNA function with the IFERROR function.
@@spreadsheetlife right after I posted the comment I figured it, the name of the table or spreadsheet was not matching what I put on the formula, so everything good! Thank you so much for the reply and what you do on this channel
Hi this tutorial was helpful. However I’m having trouble with the events not reflecting in the calendar. The though the formula reflects in the calendar. Could you please help me
Of course. First make sure that you changed the table name to 'Events'. As I've helped others, this seems to be the main issue. If that doesn't solve the problem, you are welcome to share your spreadsheet with me by sending me an email at info@speadsheetlife.com and I can take a look at it for you.
Okie dokie. Let's see if I can help. As I mentioned above, double check that you changed the table name to 'Events'. As I've helped others, this seems to be the main issue. It that still doesn't solve the problem, write your formula here so I can take a look at it, or send me your spreadsheet at info@spreadsheetlife.com. I will do what I can to help. 😊👍
I have an excel event database (table) that lists the events by rows, not columns (I.e column A date, column B event name, column C event cost). How can it be done ?
You would have to use HLOOKUP or INDEX & MATCH instead of VLOOKUP. If you'd like more info, you are welcome to send me an email at info@spreadsheetlife.com.
These two videos are so easy to follow and I had no trouble building the calendar for 2024. But when I change the year to 2025, the events don't fill into the calendar. Can you please let me know why? Thank you so much!
That's so amazing to hear! 😄🎉 For your question - the calendar will pull events based on the date you enter in the events table. So if the date is a 2024 date, it will only show up in 2024. You have to enter a new line item for the event with a date that is in 2025. Hopefully this makes sense and helps. Let me know if you have more questions. 👍
What if you want to have more than 5 events per day? I plan to use this for the training courses we plan to offer to employees. We will need to offer different classes throughout the day, some simultaneously, with different trainers.
To add more than five events, you'd need to expand the events table, add space below each date in the calendar, and then copy down and modify the formulas accordingly. So it's certainly possible, but it will take a little bit of work. Let me know if this helps. I'm not sure if it will be enough for what you're describing. But it might be.
So there is "a way" to do something like this, but it is a little involved. If are in interested in learning more about it, send me an email at info@spreadsheetlife.com.
For this particular build, there really isn't an easy way to implement this. It's certainly possible, but it would take a good amount of extra work unfortunately.
If you complete the full tutorial, then changing the year selection should automatically change which events show up on the calendar itself. For example, if you change the year from 2024 to 2025, the calendar should also update to show only the 2025 events.
It might be easiest to help via email rather than in the comments. Send me an email at info@spreadsheetlife.com, attach your spreadsheet so I can look at it, and we can go from there! 😊👍
Did you change the table name of the table on the Events worksheet to 'Events'? This might be the issue. If that doesn't solve it, you can always send me an email at info@spreadsheetlife.com. Attach your spreadsheet, and I can take a look at it for you. 👍
I'd love to help. It may be easiest to help via email rather than in the comments depending on the issue. I encourage you to send me an email at info@spreadsheetlife.com, and we can go from there! 😊👍
So this build was specifically designed for Excel. There is a way to design it a little differently for Google Sheets though. The best way I can help is for you to send me an email at info@spreadsheetlife.com with a link to you spreadsheet. Then I can take a look at it and will be better able to help.
@ omg you replied hi! I followed your video and everything worked fine when I had set up the events table with all my January dates. Then I went to add in my dates for December of this year and it wouldn’t show. I’d switch it to Jan 2024 and that displayed fine but December 2025 nothing. I re-started the events part from the beginning and January doesn’t work right anymore at all 😅. I think all of my dates match format wise. The only thing I know I didn’t do exactly is copy the calendar sheet, delete everything and rename to Events, then make the events table. I just added a new sheet and then made it so I’m not sure if that is causing the issue? I wouldn’t think so but I’m not too familiar with everything excel yet. The dates on the calendar are custom formatted to D so it displays just the day. It’s the full mm/dd/yay on the events table. Is that the issue?
Hi!! 😊 So it looks like you definitely made some changes to try and figure it out which is great! However, it does make it a little difficult for me to guess what the problem might be. My best advice is for you to send me an email at info@spreadsheetlife.com, attach your spreadsheet, and I can look at it for you. 😊👍
Hi I am having trouble getting multiple events to show? What am I doing wrong?☺️ Sure! Here’s your text in English: And when I try to highlight the dates in the months, it goes wrong. I'm unable to make the selected month appear in black.
Hello! what version of Excel are you using? I made my calendar with events on Google sheet but when I downloaded it. I was all messed up. Please help ^_^
Hi there! So this build will work with any version that is Excel 2013 or later. So it's pretty backwards compatible. However, it's not designed for Google Sheets. That's probably why you are having issues. If you'd like to create a similar calendar in Google Sheets, check out this video instead. ruclips.net/video/bwcughyS5PQ/видео.html
@@spreadsheetlife Keep up the good work! ^_^ Do you know about coding in Google Sheets? I was afraid of spreadsheets before I felt like I didn't know how to do them, but thanks to your tutorials, I've learned a lot! I graduated as an I.T. and I'm currently learning how to code in Google Sheets hehe I just felt excited learning new things... if you do, can you please make a tutorial about it? ^_^
I will do my very best to keep it up! 😄👍 As far as coding in Google Sheets, do you mean GAS (Google Apps Script)? If so, that might be a little out of scope for what I'd like this channel to be at the moment since I don't want to the content too far out of reach from beginners and average Google Sheets users. However, it still might be something I consider a little later on. It just may be a while. But I will most definitely take your suggestion into account, and who knows, if enough people ask me about it, I just might do it sooner rather than later. 😄
I do have a way you can color code specific types of events, but it is a little tedious. Send me an email at info@spreadsheetlife.com, and I can show you how to do it.
Did you change the table name to "Events"? That might be the issue. Another issue may be your version of Excel. If you are using an older version of Excel, replace the IFNA function with the IFERROR function. I hope this helps.
Oh ok. Then my advice is to get rid of the IFNA function entirely. Even the IFERROR function is not compatible with Excel 2010 I don't think. I'm sorry that is the case.
@@spreadsheetlife Anyway Thanks for the information and for the great ideas for creating calendar like this. God Bless you. Also thanks for the help to discover the problem that occurred towards me
Yes you can. Just add each holiday as an event. Unfortunately, there is no easy way to automate this though. You will have to type in the holiday's manually.
Did you change the table name to "Events"? As I've helped a few others, I am finding that this is the most common problem. If that is not the issue though, try replacing the IFNA function with the IFERROR function. The IFNA function is not compatible with older versions of Excel. I hope this helps.
If I make this all and the last step the ...=IFNA(IF(VLOOKUP(B7, EVENTS,2,FALSE)=0,"",VLOOKUP(B7,EVENTS,2,FALSE)),"") GIVE ME # NAME? ..... WHERE'S the mistake how can I know it
The #NAME? error shows up when Excel does not recognize the text in a formula. This is usually due to a misspelled function. So try to make sure that you have all of the function names spelled correctly.
It could also be that you are using an older version of Excel that does not have the IFNA function. If that's the case, try using the IFERROR function instead.
hi, can you help me? i already follow the steps according to the video, and the list doesn't appear on calendar. i tried to identify the problem, try ifna to iferror, change the table name but it doesn't work. can you take a look at my spreadsheet?
Straightforward and helpful! Thank you!
Best tutorial I've ever seen. THANK YOU!
Thank you so much for your kind words! 😊
easy to follow, calm tone, no loud annoying music, Thank You!
I'm so glad you enjoyed the video!! Thank you for watching! 😄
These two tutorials were absolutely awesome. It's going to make our training schedule so much easier to maintain - Thank you!
That's so amazing to hear!! Thank you so much for watching, and I'm super glad you got so much out of them.
This was so well done. Even for beginners, you can easily follow along! WELL DONE!!!
Thank you so much for your kind words. It really means a lot! 😊
You’re literally a life saver. Thank you so much for sharing this!
Of course! I'm so happy to help. Thank you for watching! 😊
So I’m 76 and have worked using Excel in my former work place. Since retiring and rather than sitting on the Couch I took up learning VBA. Found some channels but most presenters rush along with the instruction. But am so happy to find your channel as you are Cristal Clear when speaking and I found I could keep up with you. Pleased to say developed the Event Calendar which I love. Bonus I now know how to make a Copy after your instruction. My sincere Thanks Ryan and of course I have Subscribed and Liked so will staying with your channel and checking out previous videos. 👍👏
Thank you so much for your kind words and feedback. It's incredible to hear that my work is so appreciated. Thank you. You really made my day. 😊
I'll do my very best to keep up the good work! 👍
OMG! That was the BEST excel training video EVER! Thank you so much for teaching us this calendar! I feel so empowered by this training! Thank you Thank you!
Thank you so much for your kind words. You really made my day, and I'm so glad that this video had so much value to give to you. 😊
@ I just wish you could see my beautiful spreadsheet I made following both part 1&2. 🤩. Your videos was just what I was looking for! It looks sooo professional and it goes into the future with all the years! I made a ‘leave’ calendar and changed colors for the events adding in rules once they were on the calendar. It came out so nice! I’m just impressed that you know all those hard formulas!
That's so amazing! 😄
If you'd really like to share, you are welcome to send me an email at ryan@spreadsheetlife.com. Attach your spreadsheet, and I will see it. I'd love to see how it all turned out! 😊
@@spreadsheetlife Okay will do when I get back from vacation!
😊👍
I'm an EA creating annual calendar of events for my teams and I so appreciate how easily you helped explain everything!
That's so amazing to hear!! Thanks for letting me know, and I hope you enjoy using the calendar! 😊👍
Thank you so much. Such easy-to-follow steps.
So glad you found it easy to follow!! 😄🎉
THANKS SO MUCH!!! These tutorials were AWESOME. I'm trying to help a friend who isn't very tech savvy make an updatable events calendar for a nursing home. This is exactly what we need. I do need to figure out how to make each month a separate page and "pretty" it up so she can print it, but this gives me what I need to do it. I would also love to know how to make events not in the current month either not show up or show up in a different color.
I'm so glad that you have gotten so much out of these tutorials. If you are still struggling to figure out those last details, send me an email at info@spreadsheetlife.com, and I may be able to help you figure out those last few things.
Hi,
This is so amazing bro, I think i had skipped a step but i finally did it. This is the best tutorial ever.
Thank you so much for your kind feedback! Congratulations on finishing it!! 😄👍
You are the absolute best teacher, thank you!
Thank you so much for your kind words. I do my best, and I'm so glad that you enjoyed the video! 😊
I just wanna say thank you this worked out so very well! I had a couple issues but only mine. I didn't realize the table name was so important but I fixed it and now it's working really well thanks again 🎉
That's so incredible to hear!! 😄🎉
Thanks for the feedback, and I'm so glad it worked out.
This was a great tutorial. Thank you so much for sharing. Already put my calendar to good use :)
That's so awesome to hear! Thanks so much for watching! 😄
Thank you so much for this help ! Together with te events this is so good.
I'm so glad that you have enjoyed and gotten value out of this video! 😄👍
It took me awhile, but I did it! I had trouble with making my own table, so I just used an Excel table. Thanks!
I'm so glad you got it all to work out in the end. Awesome job!! 😄👍
Hi, amazing video! Could you maybe do one but with a yearly overview? Really appreciate the way you explain things!
Thank you so much! And that's an awesome idea. I will add it to my list. Thank you for the suggestion. 😊
Thanks so much for your easy to follow tutorial. I was hoping to add a little something to mine. What can I do to have a place to enter my events at the top of the table ?
I'm so glad you enjoyed the tutorial! 😊
For your question, could you clarify a little bit for me? I apologize that I don't quite understand what you're asking.
Thank you for this amazing tutorial! Can you show us how to make the events recurring so that I don't have to put the date in manually for every event for every year pretty please?
Thank you so much for watching! 😊
For your question - I'm so sorry this is the case, but this design doesn't have an easy way to incorporate recurring events. The only way to do it is manually like you described.
This was such a good tutorial thank you so much!!
You're very welcome. Thank you for watching! I'm so glad that you enjoyed the tutorial.
Again, Excellent tutorial.
😄👍😊
This is wonderful tutorial and a very helpful sheet you helped create. BIG Thank You. Only thing missing and I would be great to have, recurring event if there is event every week/biweekly or monthly how to have it once and can show up in calendar
I saw the responses, seems no easy way for reoccurring events. No worries. But again, Great Work and Wonderful Tutorial :)
Thanks so much. Yeah, I'm sorry there isn't an easy solution for recurring events, but I'm so glad that you enjoyed the video!! 😊
He's done a great job
Thank you so much. 😊
Thank you so much! I would like to add categories to events? Or add different colors for internal and external event. Is that possible?
Of course! Thank you for watching!
And that is a great question. So there isn't really a way to directly do that, but I do have some suggestions for you. One thing you could do is place internal events in one of the first two or three event spots and external events in the last two or three. Then change the font color for those respective rows on the calendar page.
Another thing you can do is add conditional formatting to the calendar to color events that contain a certain word, character, or phrase a certain color. This would be more difficult to implement though.
@@spreadsheetlife Thank you for the quick reaction. The last one would be the one I'm looking for. Do you have a formule suggestion?
I do! And I just saw your email. I'll tell you there!
Thanks for your tutorial about dynamic calendar i followed and did it, can you also show how to make appointment schedule for patients where i can see the details next to the calendar and it will change if i click on the specific day on the calendar.I still want the calendar plain as it is. I hope you notice, thank you very much
That's awesome! I'm glad you were able to follow and complete it.
As for your question, this sounds like an slightly more involved question. If you'd like, send me an email at info@spreadsheetlife.com, and we can discuss the details there. It's a little easier than chatting through the comments.
Loved this two part explanation. I was wondering if it would be possible to do the opposite? Essentially, to fill in data in the calendar and have that auto populate a list. Also, have each month retain the information when toggling fro month to month. Is this possible?
I'm so glad that you enjoyed the part two explanation! 😄
For your question - so it is possible to add the data to the calendar itself and then have it show up in a list...but...you won't be able to toggle the month to month and have that information stick. So that's the down side unfortunately.
Amazing - thank you!!
You're very welcome! 😊
Thank you for watching!
That is excellent ! Many thanks
You're very welcome! Thanks for watching! 😊
These two tutorials were great and very easy to follow. I do have a question though is there a way to make events recurring, i.e., weekly monthly, etc, and even annually, so birthdays come each year?. Also for adding events that go more than one day would be useful.
I'm so glad that you enjoyed these tutorials! 😊
For your questions, I've had a few other people ask me this, so they are great questions. Unfortunately, with this specific design, there really isn't an easy way to add recurring events or events that span multiple days. It's just not something I thought of when I put it together. I'm so sorry that this is the case.
Fantastic tutorial! In my situation, we organize our list a bit differently. Is there a way to bypass a date that returns a 'null' result, & check further down the column for another occurrence? Thanks!
Hmmm...interesting question. I kind of understand, but not quite. Could re-phrase? I might have an answer if I better understand your question.
@@spreadsheetlife Absolutely. We have an annual training calendar that regularly has multiple events on a single day. Many of the events reoccur on differing schedules (Annual, Quarterly, Monthly) or are one time occurrences. Rather than try to keep track of everything that happens on one row, it is much easier to assign a row to each event, and attach the date to that row. This results in having multiple rows with the same date. Unfortunately, VLookup stops searching down the column when it finds a match. What I have done for now is to have each category on its own table/sheet, and modified the formula so that each row on the calendar draws from a different table. It would simplify the workbook if there was a way to have a formula that found all occurrences of a date and pulled that data into the dynamic calendar.
Ok, I gotcha. Let's see, my first instinct is to suggest using the FILTER function.
To better explain how to utilize the FILTER function, it would be quite a bit easier to discuss via email rather than in comments. You can email me at info@spreadsheetlife.com.
@@spreadsheetlife Great, Thanks!
No problem! 😄👍
Thank you so much. It is very helpful.
I want to add business days under each calendar date.
Also holidays should not be considered as business day. Please help with the formula
Let me see if I understand your question. Are you asking how to enter text values in the cells below the date value that indicate if the day is a business day or not? Or is it something different that you are asking?
Thank you! Great tutorial. How do you make it change with the year?
Thank you so much! I'm so glad that you enjoyed the tutorial. If you followed along and completed the tutorial, then the calendar will automatically update whether you change the month or the year. So no changes are needed. 👍
Thank you but how do you put in an event with a range of dates without entering it into the Events table for each individual day?
Unfortunately with this design, the only way to add an event that spans multiple days is by manually entering that event for each individual day. I'm sorry that's the case.
new subscriber here. love the content! I have a question though with this dynamic calendar, what if say for example I put on one of the events on a date the word "all activities cancelled due to holidays" or just the word "cancelled". i need the whole block to turn light red(including the cells that already has activities on it . what would be the best formating formula for that?😊
Thank you so much for subscribing!! I'm so glad to hear that you enjoy the content so much! 😄
As for your question - this is a tough one. My best advise for you is to send me an email at info@spreadsheetlife.com with a copy of your file. I will give it my best try to figure it out. No promises though. This one sounds tough like I said. But I can try.
Can we use this to make dynamic calendars for 4 people to export their data from their calendars?
That might be a stretch for what this template is capable of. Your are welcome to give it a try, but it might work out. It's hard to say without some more context.
Thanks, can you add time in hourly fashion as well?
You can manually type in the time within the event description, but that's about all you can do for this specific build.
I've done this and now on my events sheet (I've extended the dates to include all of next year) there are a bunch that are showing up as ################
How do I fix this?
This error could mean one of two things.
1 - The column is not large enough to display the date. To fix, expand the column width.
2 - You have negative date values. To fix, ensure that there are no negative date values.
This was great, is there a way to show multiday events?
I'm so glad you enjoyed the video! Unfortunately with this design, the only way to show multi-day events would be to manually enter the event for each day. I'm sorry that's the case.
Can u make the events ini same sheet? If u change the month the table of event change too?
So the way Excel works, you can't type the events directly into the calendar and also have them update whenever you change the month. I'm sorry that is the case.
Is there a way to have an event cover a date range? For example, Wed-Fri of a particular week? Or do I need to copy/past for each date?
Good question. For this specific design, you'd have to copy/paste for each date. I'm sorry that's the case.
Is there a way to change the color of the events on the calendar without having to edit each separately
Yes, this is possible. But it will require some extra conditional formatting rules. If you're interested in learning more, send me an email at info@spreadsheetlife.com.
Hi! I have been following the guide since Part 1 on Google Sheets. Now I have encountered and issue that the event dates does not go over to the calendar side. I have tried fixing it but am unable to. Could you explain why do we have to but B7 in the Vloopup? And after wards the number changes according to the row -1.
Let me see if I can help. So VLOOKUP references B7 because it wants to look at the date that's in that cell and use it as the lookup value. The VLOOKUP function then references the Events table and searches for that lookup value (from B7). Once it finds that date, it returns the corresponding value in the 2nd column (because we enter a 2). So that's what is going on.
Now if that makes sense, and you still can't figure it out, send me an email at info@spreadsheetlife.com, and attach your file. I will take a look at it and see if I can help! 😊
Hello my friend. First of all, thank you very much for the video that you uploaded. Very helpful. As I see, I have to use (;) instead of (,), and I don't know why,
but it's OK.
But my real problem is that I have an issue when I'm trying to take the events. I don't know if I'm doing anything wrong or what. It returns this: #NAME?
I saw your email and sent you back a response. 😊👍
I have the same issue?
I saw and responded to your other comment. 😊👍
This was great and very clear - thank you! Two questions: (1) Can I have an event that spans multiple dates or the entire month? (2) Can I pull through dates and event name from another sheet?
That so amazing to hear! Thank you for watching. As for your questions:
1 - Unfortunately not with this design. You'd have to enter in the event manually for each date. I'm sorry that is the case.
2 - Yes you can, but the formulas and specifics will depend on your specific situation.
Thank you sooooooooooooooo much for this great video, please next video if you can add that how we can make the event frequent monthly or Quarterly of Semi annual.
You're very welcome. Since I've already made four calendar videos (two for Excel and two for Google Sheets), I probably won't make another one for a while. But if I do, I'll be sure to include recurring tasks as you have described. I'm sorry it may be a while though.
Hi Ryan, Great videos, however, I'm not sure if I'm doing something wrong when I change month some of my January Events eg 30th January shows on the February, how do I stop this?
Hi there! So the calendar will always display the first Sunday before the 1st of the selected month. This is what you are seeing events from the previous month at the beginning. Now there is a way you can adjust the formulas so that the events don't show up. Would you be interested in learning that formula?
@@spreadsheetlife Yes please always looking to learn new things
Okie dokie. So you would change the formula in cell B8 to the following:
=IF(MONTH($J$5)MONTH(B7),"",IFNA(IF(VLOOKUP(B7,Events,2,FALSE)=0,"",VLOOKUP(B7,Events,2,FALSE)),""))
Then copy this formula down just like we did in the video, and be sure to update the references to B7 and change those VLOOKUP numbers to 3, 4, 5 etc. So your formula in cell B12 will end up like this:
=IF(MONTH($J$5)MONTH(B7),"",IFNA(IF(VLOOKUP(B7,Events,6,FALSE)=0,"",VLOOKUP(B7,Events,6,FALSE)),""))
Then copy this formula over to the right for the whole row. Then copy, and paste for the rest of the rows in the calendar. I hope this helps. 😊👍
@@spreadsheetlife Thanks that worked perfectly. I tried an if statement, but didn't include the Month function, so it didn't work
Awesome! I'm glad you got it to work. Yeah, including the MONTH function is definitely the tricky part in this case. You were on the right track with the IF function. Just needed that extra piece. 😊👍
What if I need more Event's to vover per day? I need to add new rows and copy paste formulas?
Yes, you would have to add more rows underneath each date, and more columns to the event table.
Hello, thankyou for the tutorial! It really helped. Can you also also tell me what to do if the event date is like a period for eg: 02nd - 06th October 2024. Please reply
Hi there! I'm so glad that you enjoyed the tutorial. Unfortunately, there isn't an easy way to make an event span multiple days. Probably the best solution is to just re-enter the event for each date in falls on. So enter the same event for all four days.
Is there a conditional format formula to make the events that are not in the present month highlight in grey, similar to the dates that are not in the current month?
Yes there is! Send me an email at info@spreadsheetlife.com, and I can show you how to do it.
Thanks a lot
You're very welcome! 😊
Thank you sooooooo much
You're very welcome!! 😄👍
Hi, Thank you so much for this, it was really helpful. I’m having trouble to connect the events, when I add the IFNA formula it shows the #NAME? Error.
I look and every formula seems to add up, so I don’t understand why it shows this. Any advice?
Hmmm...what version of Excel are you working in? If you are working with an older version of Excel, try substituting the IFNA function with the IFERROR function.
@@spreadsheetlife right after I posted the comment I figured it, the name of the table or spreadsheet was not matching what I put on the formula, so everything good!
Thank you so much for the reply and what you do on this channel
Oh that's awesome! I'm glad you figured it out. 😄
And of course. Thank you for watching and for your support! 😊
I did exactly same. But the calendar tab isn't showing anything even after changing months... I filled the event tab
Fascinating. You are welcome to send me an email at info@spreadsheetlife.com, and I can try to take a look at it for you.
Hi this tutorial was helpful. However I’m having trouble with the events not reflecting in the calendar. The though the formula reflects in the calendar. Could you please help me
Of course. First make sure that you changed the table name to 'Events'. As I've helped others, this seems to be the main issue. If that doesn't solve the problem, you are welcome to share your spreadsheet with me by sending me an email at info@speadsheetlife.com and I can take a look at it for you.
@@spreadsheetlife Hi, I had the same issue too, the formula is correct but events are not reflecting in the calendar. Hope you can help... thanks
Okie dokie. Let's see if I can help. As I mentioned above, double check that you changed the table name to 'Events'. As I've helped others, this seems to be the main issue. It that still doesn't solve the problem, write your formula here so I can take a look at it, or send me your spreadsheet at info@spreadsheetlife.com. I will do what I can to help. 😊👍
thanks a lot, this really helped !
That's awesome. I'm so glad that this video was helpful! 😄
You are the best 🎉 QQ upto how many events can show on one day ?
Thank you so much for your kind words! 😊
And good question. With this design, you can only display up to five events on one day.
I have an excel event database (table) that lists the events by rows, not columns (I.e column A date, column B event name, column C event cost). How can it be done ?
You would have to use HLOOKUP or INDEX & MATCH instead of VLOOKUP. If you'd like more info, you are welcome to send me an email at info@spreadsheetlife.com.
Would it be possible to make an events calendar this way but in a weekly view format with events down the side?
Yes, that is definitely possible. You'd have to change several things in your approach, it is certainly possible for sure.
These two videos are so easy to follow and I had no trouble building the calendar for 2024. But when I change the year to 2025, the events don't fill into the calendar. Can you please let me know why? Thank you so much!
That's so amazing to hear! 😄🎉
For your question - the calendar will pull events based on the date you enter in the events table. So if the date is a 2024 date, it will only show up in 2024. You have to enter a new line item for the event with a date that is in 2025. Hopefully this makes sense and helps. Let me know if you have more questions. 👍
@@spreadsheetlife Ok, thank you. I will work on it again tomorrow and let you know if I have any questions.
😊👍
I had completed all steps, but events is not showing in may calendar date
???
Did you rename the table to "Events?" This might be the issue.
Hi is it possible to use similar formulas for Fiscal Year
Hi there! I'm sorry, I don't think I understand your question. Could you rephrase the question for me?
What if you want to have more than 5 events per day? I plan to use this for the training courses we plan to offer to employees. We will need to offer different classes throughout the day, some simultaneously, with different trainers.
To add more than five events, you'd need to expand the events table, add space below each date in the calendar, and then copy down and modify the formulas accordingly. So it's certainly possible, but it will take a little bit of work. Let me know if this helps.
I'm not sure if it will be enough for what you're describing. But it might be.
How would I colour code the events columns and then link this between both spreadsheets?
So there is "a way" to do something like this, but it is a little involved. If are in interested in learning more about it, send me an email at info@spreadsheetlife.com.
Hi, what to do? I am getting an error of this function? Please help
Could you write your formula here so I can take a look at it? I will do my best to help. 👍
I’m fairly new to all the functions, but is there a way to make repeating events? Say a birthday each year?
For this particular build, there really isn't an easy way to implement this. It's certainly possible, but it would take a good amount of extra work unfortunately.
@@spreadsheetlife I was thinking I could just change the year on the birthdates for one… thanks for responding.
Gotcha. I'm sorry the solution isn't as straightforward. I still hope you enjoy the template though.
@@spreadsheetlife I’ve learned a LOT. Thank you for sharing your knowledge.
Of course! Thank you for watching! 😊
What is the formula to change the year on the calendar sheet to reflect and update the events from year to year, ie:- 2024 to 2025? Please reply
If you complete the full tutorial, then changing the year selection should automatically change which events show up on the calendar itself. For example, if you change the year from 2024 to 2025, the calendar should also update to show only the 2025 events.
What if I want to enter an event that reoccurs on a monthly or even weekly basis?
Unfortunately, based on this design, you'd have to type the event in manually for each occurrence. I'm sorry that's the case.
hi i am having trouble getting multiple events to show? how would i do that
Have you completed the tutorial all the way through? Or are you stuck at a specific spot?
Can you help me,?please .. , the table array in the formula cant be read :( , took a check of the spelling it matches ,,what should I do?
It might be easiest to help via email rather than in the comments. Send me an email at info@spreadsheetlife.com, attach your spreadsheet so I can look at it, and we can go from there! 😊👍
@@spreadsheetlife will do! thank you!
i dont know what happen but my events doesnt connect in the calendar😢😢😢
Did you change the table name of the table on the Events worksheet to 'Events'? This might be the issue.
If that doesn't solve it, you can always send me an email at info@spreadsheetlife.com. Attach your spreadsheet, and I can take a look at it for you. 👍
Using office 16 ...Events not upload in calender 😞. ..... Can u helpp ?
I'd love to help. It may be easiest to help via email rather than in the comments depending on the issue. I encourage you to send me an email at info@spreadsheetlife.com, and we can go from there! 😊👍
7:26 getting error here ??
I mentioned it in my response to your other comment, but did you rename the table to Events? That might be the issue.
I tried on Google Sheet, but the event I created doesn't appear on the calendar. Is the formula used in Google Sheets different? Please help me
So this build was specifically designed for Excel. There is a way to design it a little differently for Google Sheets though. The best way I can help is for you to send me an email at info@spreadsheetlife.com with a link to you spreadsheet. Then I can take a look at it and will be better able to help.
@@spreadsheetlife Thank you. I have sent an email yesterday and it has been answered and helped too. Thank you very much ryannn ❤️
Of course! I'm happy to help! 😊👍
@@kyhyuHi! I also tried to do it in google sheet. Can you tell me what the formula should look like there, if you have already been helped?
Hey there! You are also welcome to send me an email at info@spreadsheetlife.com, and I can try to help! 😊👍
This worked for me at first but then I added the dates for December in the event sheet and it no longer works properly
How fascinating! I'm sorry your having issues. Did you make sure that you have the year portion of the date correct? This might be the issue.
@ omg you replied hi! I followed your video and everything worked fine when I had set up the events table with all my January dates. Then I went to add in my dates for December of this year and it wouldn’t show. I’d switch it to Jan 2024 and that displayed fine but December 2025 nothing. I re-started the events part from the beginning and January doesn’t work right anymore at all 😅. I think all of my dates match format wise. The only thing I know I didn’t do exactly is copy the calendar sheet, delete everything and rename to Events, then make the events table. I just added a new sheet and then made it so I’m not sure if that is causing the issue? I wouldn’t think so but I’m not too familiar with everything excel yet. The dates on the calendar are custom formatted to D so it displays just the day. It’s the full mm/dd/yay on the events table. Is that the issue?
Hi!! 😊
So it looks like you definitely made some changes to try and figure it out which is great! However, it does make it a little difficult for me to guess what the problem might be. My best advice is for you to send me an email at info@spreadsheetlife.com, attach your spreadsheet, and I can look at it for you. 😊👍
@ oh my gosh thank you! 😭 I emailed you 😁
😊👍
where does "2" come from?
Are you asking about the 2 in the VLOOKUP function? This causes the VLOOKUP function to return the value from the second column in the Events table.
Hi
I am having trouble getting multiple events to show? What am I doing wrong?☺️
Sure! Here’s your text in English:
And when I try to highlight the dates in the months, it goes wrong. I'm unable to make the selected month appear in black.
Send me an email at info@spreadsheetlife.com. Attach your spreadsheet to the email. I will be able to help more that way. 😊👍
Hello! what version of Excel are you using? I made my calendar with events on Google sheet but when I downloaded it. I was all messed up. Please help ^_^
Hi there! So this build will work with any version that is Excel 2013 or later. So it's pretty backwards compatible. However, it's not designed for Google Sheets. That's probably why you are having issues. If you'd like to create a similar calendar in Google Sheets, check out this video instead.
ruclips.net/video/bwcughyS5PQ/видео.html
@@spreadsheetlife Thanks for your response
That's amazing to hear. I'm so glad that you got it! 😄
And thank you so much for the sub! I'll do my best to keep good content coming.
@@spreadsheetlife Keep up the good work! ^_^ Do you know about coding in Google Sheets? I was afraid of spreadsheets before I felt like I didn't know how to do them, but thanks to your tutorials, I've learned a lot! I graduated as an I.T. and I'm currently learning how to code in Google Sheets hehe I just felt excited learning new things... if you do, can you please make a tutorial about it? ^_^
I will do my very best to keep it up! 😄👍
As far as coding in Google Sheets, do you mean GAS (Google Apps Script)? If so, that might be a little out of scope for what I'd like this channel to be at the moment since I don't want to the content too far out of reach from beginners and average Google Sheets users. However, it still might be something I consider a little later on. It just may be a while. But I will most definitely take your suggestion into account, and who knows, if enough people ask me about it, I just might do it sooner rather than later. 😄
how would i colour code specific types of events
I do have a way you can color code specific types of events, but it is a little tedious. Send me an email at info@spreadsheetlife.com, and I can show you how to do it.
Please i also need that colour code specific types of events. Plz help me. Very urgent.
Send me an email at info@spreadsheetlife.com, and I will do my best to help.
WHY DID MINE BECOME ERROR WHEN IT COMES TO THE LAST PART FORMULA? CAN SOMEONE HELP ME
Did you change the table name to "Events"? That might be the issue.
Another issue may be your version of Excel. If you are using an older version of Excel, replace the IFNA function with the IFERROR function. I hope this helps.
My version is 2010
Oh ok. Then my advice is to get rid of the IFNA function entirely. Even the IFERROR function is not compatible with Excel 2010 I don't think. I'm sorry that is the case.
@@spreadsheetlife Anyway Thanks for the information and for the great ideas for creating calendar like this. God Bless you. Also thanks for the help to discover the problem that occurred towards me
Of course! Thank you for your patience with me, and thank you for watching. All the best! 😊👍
hello i got a problems with fx vlookup problems (#name? ) can u help plz
I'd love to help! Could you paste your formula here in the comments so I can take a look at if for you?
@@spreadsheetlife HELLO I GOT IT THANK YOU
BUT TELL ME IF WE CAN ADD Public HOLIDAY IN IT
Yes you can. Just add each holiday as an event. Unfortunately, there is no easy way to automate this though. You will have to type in the holiday's manually.
@@spreadsheetlife THANK YOU
You're welcome! 😊👍
Formula not working @here plz assist me
Did you change the table name to "Events"? As I've helped a few others, I am finding that this is the most common problem.
If that is not the issue though, try replacing the IFNA function with the IFERROR function. The IFNA function is not compatible with older versions of Excel. I hope this helps.
@@spreadsheetlife Thank you so much bro, now it's 😘working
Ok awesome! I'm glad you got it to work. 😄
If I make this all and the last step the ...=IFNA(IF(VLOOKUP(B7, EVENTS,2,FALSE)=0,"",VLOOKUP(B7,EVENTS,2,FALSE)),"") GIVE ME # NAME? ..... WHERE'S the mistake how can I know it
Are you working with an older version of Excel? This may be why you are getting the error.
Try switching the IFNA function with the IFERROR function.
@@spreadsheetlife still not solve this problem....please help me
Try sending me a copy of your spreadsheet at info@spreadsheetlife.com. This way, I can take a quick look at it.
I keep getting a name error
The #NAME? error shows up when Excel does not recognize the text in a formula. This is usually due to a misspelled function. So try to make sure that you have all of the function names spelled correctly.
It could also be that you are using an older version of Excel that does not have the IFNA function. If that's the case, try using the IFERROR function instead.
Hello. Help me please
What can I help you with?
I feel too old to learn.
You're never to old to learn. I really hope this video has helped. Thank you for giving it a try! 😊
hi, can you help me? i already follow the steps according to the video, and the list doesn't appear on calendar. i tried to identify the problem, try ifna to iferror, change the table name but it doesn't work. can you take a look at my spreadsheet?
I can for sure! Just send me a copy of your spreadsheet at info@spreadsheetlife.com.
Using Excel for Mac and getting NAME error on IFNA formula. Can you help?
Yes. Try replacing the IFNA function with the IFERROR function. Let me know if this works for you!
@@spreadsheetlife Very good! Thanks. It worked.
Yay!! I'm glad it worked! 😄
How can i add more events to the calendar. Help me please
You can expand the events table by clicking on the bottom-right corner and dragging down. The table can be expanded as much as you need. 👍