Forum Discussion
Populate dynamic calendar with table data
I have a dynamic calendar set up and a table with data, I need help figuring out the right formula to use to have the calendar populate with the tasks from the table.
For example, one task may be 'Post video' which I would want to show up on the due date. Another task may be, newsletter campaign which I would want to post for the length of time from start date through due date, and show on each date in between.
The second layer of this project is to then color code the calendar based on the column 'Post type'. For example, all newsletters in a blue cell, all videos in a green cell, etc.
I've attached a sample workbook, can someone help me set it up with this functionality or explain how I should build that forumla?
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.
72 Replies
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.
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
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!!
