How to create a Calendar, that auto populates data?

Hi All,

I need to create a calendar that displays due dates for corresponding company departments and products. Currently, I only have a list. However, I want to create a calendar that auto populates (from the list I currently have) for the next few months. How do I do this?

Thanks in advance!

Attach the list that you have (anonomising any confidential data), and I'll see if I can incorporate into one of my calendar files. Give as much detail as you can about how you want the calendar to appear.

To attach a file, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Hi Pete,

Thanks for the prompt follow up and your willingness to help. Here is what I need:

1) A calendar for each month, for the remainder of the 2018 year
2) For each day to have a drop down options for the following: Product, Department, Task
3) For each item to auto-populate from "Sheet 1" to the "Calendar" and/or have a drop down option for each item (ie Product, Department, Task)


What I've included:
1)A list of info (on "Sheet 1") to be included on the calendar
2)An example of how I would like the calendar to formatted


Hopefully this helps provide some clarity. Please let me know if I can help with anything else.

Thanks so incredibly much!

It appears the attachment option isn't working

Maybe this time will work...

In answer to the points you have raised, I have developed several calendar files for posters on the forum. The general approach is to have a sheet where a list of "activities" can be recorded in any order - in essence, there is a column for the date and another column for the description/title, although there might be other fields which need to be included (as you seem to want). In many of the files the events occur on a single day (as yours appear to be), but I have also developed solutions where the events are spread over several days, so in this case a start and end date is specified on the Activities sheet, and formulae on that sheet automatically expand that single record into multiple records, one for each day.

The calendar is displayed on a separate sheet, and rather than have one sheet per month I set it up to display a single month at a time, where the year and month of interest can be selected via two drop-downs. The layout I prefer is to have the weekdays going across the page (Sunday to Saturday) and the weeks down the page, exactly as you have shown in your example. I have found that to have equal-width days, then in landscape mode it is possible to display up to 7 events per day. Looking at your example data, there is one day when you have 11 events, so what I have done in the past is to orient the calendar in portrait mode and that can give more events per day, although the column widths are reduced. By displaying up to 10 events per day, AND a separate drop-down which can select the range (1-10, 11-20, 21-30 etc), it is then possible to see many more events, though not at the same time. Other drop-downs can be used to select particular categories of event (or all of them), so I think this covers most of the things on your list (and maybe a few others).

The main drawback that I can see from your example data is that some of the text you have is quite long, so we might need to use a smaller font to fit it in. If the calendar is only going to be viewed on screen, then we can widen the colunms, but if you intend to print it out then this might be restrictive.

Once I have selected a suitable file from my collection (which could take quite a while!!), then it will be quite easy to modify it to suit your example data. However, I am committed to be out all day tomorrow, and then I have visitors arriving in the evening for the weekend, so I'm a bit restricted for time during the next few days. Keep checking on this thread every now and again, and I WILL put something together for you.

In the meantime, I've attached an example file (developed originally for this thread:

https://www.excelforum.com/excel-for...from-data.html

which will give you some idea on what it will look like and how it works.

Hope this helps.

Pete

I was able to find a bit of time before my visitors arrive, so I have set this up for you in the attached file.

I have copied your example data into columns B to E of the sheet called Products. There is a formula in column A which essentially gives each record a unique ID based on the date. This formula has been copied down to row 1000, and you can hide this column if you wish, to prevent accidental changes to the formula (the column is coloured blue, to make it stand out). All you need to do in this sheet is to add any new data to columns B to E under the data already there, and remember that new data can be added in any order.

In the Calendar sheet there are four drop-downs which control what is displayed, and I've given these a yellow background. You can select the month and year to display using cells K5 and K6. Up to 10 items can be displayed for each day (numbered in column A), but as I pointed out last night, you have one date (20th August) which has 11 entries in the data sheet, so I have set up another drop-down in cell K10 which enables you to select the range to be displayed, in groups of 10. The fourth drop-down, in cell K15, enables you to choose between displaying Products, Tasks or Departments, as per your request.

Each of the chosen options is reflected in the title on the top row, and the display changes automatically as soon as one of the options is changed. This is all formula-driven, so there are no macros to enable in order to use it.

I have added a few comments in green on the sheets - you can remove these once you are familiar with using the file. A print area has been set up, and you can use Print Preview to see what it will look like if printed - I've adjusted the column widths to accommodate (most of) the long text strings, but these might look a bit small if printed.

Anyway, have a play about with it - I think it covers all the things that you were asking for.

Hope this helps.

Pete

You never did get back to me on this. It's been over a week, and I think it is only common courtesy to acknowledge the help that has been given.

Anyway, I'm away for a week from tomorrow.

Pete