Forum Discussion
Populate dynamic calendar with table data
Mthammon The calendar template you chose isn't well suited for what you have in mind. As most templates, it's packed with tricks and treats. This particular one is full of named formulae. Neat, but near to impossible to tweak without destroying something else. But, there are templates "out there" that can do what you need. Not too long ago, I adapted such a template for another user on this forum, though it uses the rather new FILTER function. The original template just highlighted that "something was due" on a particular date without specifying what. After my adaption it actually displays the assignment(s) due on a given day.
I took a few columns from your data sheet and dumped them in the template. See if it works for you (file attached). If not, keep searching for a better template.
Mthammon The calendar template you chose isn't well suited for what you have in mind. As most templates, it's packed with tricks and treats. This particular one is full of named formulae. Neat, but near to impossible to tweak without destroying something else. But, there are templates "out there" that can do what you need. Not too long ago, I adapted such a template for another user on this forum, though it uses the rather new FILTER function. The original template just highlighted that "something was due" on a particular date without specifying what. After my adaption it actually displays the assignment(s) due on a given day.
I took a few columns from your data sheet and dumped them in the template. See if it works for you (file attached). If not, keep searching for a better template.
I am customizing this template that you originally provided in 2021 (saving me tons of time...thanks!)
Wondering how to edit the "pop up instructions"...doesn't give the the option as comment or note.
Thanks!
Captain_T Those pop-ups are Data Validation input messages that came with the original template.
So, select all cells with similar validation rules. On the Data ribbon, Data Validation, Input Message, Clear All. OK.
Just save your work before doing this, in case you make a mistake.
Thanks again for this help!
I'm now customizing the calendar page and wondering how to duplicate a new formula in all the calendar cells (where to get the entries that show on the calendar) in some automated way? (besides copying and pasting each one...). I hope that makes sense.
This is great! Do you have a version of this that allows you to enter tasks with a duration so it will carry it across multiple days?
Did you start a new thread on this? I need to show duration also
I would also like to know the answer to this. Are you able to share a link to the new thread?
EmilyF460 No sorry! I actually cleaned up my Tech Community files the other week. Best that you start a new thread, upload your file (or a share on OneDrive, Dropbox or similar). This thread is becoming rather lengthy and becoming a mixed bag of many alternatives.
This template was so helpful! I was able to adjust it to power query all my tabs into one sheet which was great. I'm now trying to highlight today if that were the drop down calendar. Can you help with that? I'm also trying to conditional format cells with 2 assignments, is this possible?
Would you be able to provide that template to me? I am having an issue where I have multiple tasks/due dates across multiple sheets and would love to use the template Riny posted but need the tasks across all sheets to show up on the "Assignments tab" in order for the calendar views to work. I tried to do some vlook ups but its not working. I have a basic understanding of power query but have only ever done it to bring data from multiple sheets to a whole new sheet, Im not sure how you do that to bring that query to the "Assignments" sheet. Would appreciate any help!!
Apologies for not responding earlier. I somehow missed your follow-up question after my last answer. If you are still in need of help, please start a new thread, similar to what I explained below.
I'm glad you found this thread helpful, though if you want to customise the template further I would recommend that you start a new thread and share what you have created so far (upload a file here or share a link to it on OneDrive, Dropbox or similar).
Riny_van_Eekelenis there a way to have a yearly view tab for this sheet? For example have the months in the Cols where the days are for the month view and dates of each month in the rows.
I guess you can but it's probably easier to build such a calendar from scratch in order to avoid all the design quirks involved in the template dealt with in this thread.
Riny_van_Eekelen Thanks for the reply. Could you please assist me with creating a blank year view that I could have a tab like the assignment one to populate the dates?
Riny_van_Eekelen - Appreciate your work! I downloaded the first file (MHammon cal) and noticed the weekly and monthly views. Is it possible to develop a yearly view? I manage a training program for my organization and my intent is to have various events auto-populate on a yearly calendar based on the date the individual joins the organization. Generally speaking when individuals join my organization they are employed for a minimum of 5 years. Essentially, I'm looking to create a "road map" of career progression expectations during this initial employment period. Ideally, capturing the entire five years on one tab, if possible (Jan-Dec 2024, Jan-Dec 2025, etc.)
The product that I currently have is not very user-friendly as it requires me to manually change dates or locations of the calendar for every individual event.
I tried to attach my current file but the .xlxs file format is not supported. Thanks in advance for any assistance you can provide.
MGunz_6019 Perhaps it possible but it would probably require a compete redesign. Not something I would recommend as thes kind of templates are meant to be used as they come. change a little here and there and you'll probably end-up with a mess, unless you put a lot of effort into it.
Thanks for the quick response! I started toying with the existing document by adding another tab for annual data. I have it set up the way I want it, I just don't know if there is a formula I can use to pull data from the assignments tab into the corresponding month cell on the annual calendar based solely on the month and year.
I noticed that the original file you attached was also the .xlsx format. Do you know of a reason I am not able to attach a file of the same format on this post? I attached a snip of the annual tab I'm looking to create. Thanks again.
Riny_van_Eekelen I've very fortunately stumbled across your post today. It's absolutely perfect for what I'm trying to do, but unfortunately it doesn't work in Google Sheets. Do you know why this is and what I can do to resolve it, please?
F_Yt3 Well, all I know about Google sheets is that it's not the same as Excel. Many sheets might work on both platforms but not all.
Riny_van_Eekelen Hi! That template is almost exactly what I'm looking for for my grand opening calendar for work. I've been tasked to create it all by Friday, 1/20/23, and just found out this week. I've been scrambling everywhere to learn or find templates, so this is my last resort. Can you please help me create a calendar for the attached checklist items? I tried to use your template but I'm not sure how to unlock things to have my own information aside from dates. In the attachment, I have one tab that is the original checklist with the formulas I use to populate the dates since they are constantly changing, but also included one that has no formulas at all. Please help if you can!! It would be much appreciated!!
https://togos-my.sharepoint.com/:x:/p/alexis_bynum/EUMiirJZPEBAkBIQyHsZcrsBtIotv2aC3QlYQuUnjFWNOQ?e=UqmJOI
alexisbynum Perhaps like in the attached file?
Riny_van_Eekelen Hi, I have to tell you how grateful I was to find this thread. I spent the better part of the last two days trying to figure this out with no success. I was able to adapt the calendar for what we need but I was wondering if there was a way to have the "Assignment" show on a "Start Date" through an "End Date". I hope that makes sense. I tried to attach the file but it wouldn't let me.
@Riny_van_Eekelen I've added an additional column to the table with content type. I am trying to set up conditional formatting so that the assignments on the calendar view will be filled based on the content type (i.e. all videos in blue cell, all website in green cell, etc). Do you know what formula I need to use to get that to show up on the calendar that way?
Riny_van_Eekelen thanks for your help! I think the template you provided will work just fine for my needs! I appreciate the insight about the calendar template I had chosen, that answers why I was having so much trouble will any of the formulas I was trying to build on top of that.
I appreciate your time, it has saved me a lot of trouble!
