Create a calendar from a table

Hi there,

I'd like to create a workbook to track milestones of different projects in a calendar view. For instance, the first worksheet would have a table where I can list projects (Project 1, Project 2, etc.) in the top row, and milestones (Milestone 1, Milestone 2, etc.) in the first column. Then I'd populate the table with dates. The second sheet would be a calendar view that shows the milestones of each project under respective days. As an example, I'd have something like "Project 3 - Milestone 1" under January 1, 2014.

Thanks in advance!

PS: I'm using Excel 2011 on Mac OSX.

Take a look at my attachment in post#5 of this thread:

http://www.excelforum.com/excel-gene...ng-system.html

Is that the kind of thing you are after?

Hope this helps.

Pete

Thanks Pete_UK, but not quite... Using your attachment as an example, I need "School_1, ... , School_6" to be the column header, and the list of activities "Intro, Basic Principles, etc." to be the row header. I'm attaching a mockup I created in OfficeLibre just now because I don't have MS Office at home. Based on my mockup, under "Feb 2014" calendar, on 2/20/14, I'll have "Project 2 - Milestone 20" and "Project 3 - Milestone 6".

Well, that can be easily rectified. I've taken your source data and transposed it into a form that the calendar will accept (just straight copy/paste). Then I've put it into the Activities sheet, made a few adjustments to the lists in the Calendar sheet, and there you have it.

Hope this helps.

Pete

Thank you so much! The problem is that my raw data will be constantly changing (dates and projects), so I was wondering if there's a way to automatically transpose data from "source_data" into "Activities". This way, I can keep "Activities" hidden.

Will you always have 5 projects and 20 milestones?

Pete

This was just a mockup. The number of projects will be different (20+) but I will have up to 40 milestones. If I need to have a locked number of project (provided I can rename them) I'd put 50 projects total.

Well, to make full use of that calendar file, you will need to record the projects AND the milestones in the lists on the Calendar sheet, as these control the data validation choices on the Activities sheet. The named ranges associated with the projects (2) and the milestones (1) will also need to be adjusted, although these could be made dynamic to allow you to add other projects/milestones as you encounter them. There is also a facility to filter the calendar, not only by month and year, but also by Project, using the appropriate drop-downs, and these also rely on those lists being completed.

I'll take a look a bit later on, as I'm taking a break now.

Pete

I can move Project_list and Activity_list into new sheets and have "choices" and "Activity_list" to be the whole columns, right? If I populate those new sheets, would it be possible to auto-populate the column and row headers in "source_data" sheet, fill out that table with dates, and have the new data auto-populate "Activities" sheet for the calendar?

Thanks so much for your help. I really appreciate it.

I've included the necessary amendments in the attached file. You can just list the projects across row 1 of the source_data sheet, and the Milestones in column A of that sheet - formulae take care of all the other things. You may need to copy the formulae in columns D to F of the Activities sheet further down than row 130 if you have more data (it doesn't matter if you copy much further than needed), but then that sheet can be hidden.

Hope this helps.

Pete

This worked perfectly! Thank you so much!

Done and done

Dear Pete_Uk

I have come across your file, and its exactly what I am looking for in doing a project, I understand how it all works and can modify the Fields and headings without an issue, the only Issue that I can not understand is how do you increase the number of years in the drop down in K4 of the calendar tab

Thanks in advance
Delwyn

You shouldn't hijack someone else's thread (see Rule 2), but as yours is a simple query I'll deal with it here.

The years are controlled by the data validation in that cell, so select the cell, click on Data | Data Validation and then you can see the years listed in the Source box, separated by commas - just add more as you need them (and maybe remove some of the older ones).

Hope this helps.

Pete

Pete_UK

Thanks for the quick response.
I understand hijacking the thread was wrong, but trying to create a new thread and link back to this one on the iPad is not very easy.

Regards

Well, thanks for the rep - I presume you were able to make the changes and solve your particular problem. Hope the file works well for you.

Pete