How to Represent Multiple Project milestones in Gantt Chart

[justjazziness]

Hello!

I am trying to create a chart that displays milestones for over 50 projects. This will be used as a snapshot to show duration of project stages, as well as final project deadline and forecast of entire project timeline. The purpose is to summarize what projects have what items due in the next three months. I want to sort the chart by project deadline, so that soonest due display at the top for a quick understanding of what's going on for the month so we can better manage staffing needs.


The elements needed in the chart

-calendar display of this month, plus two more months

-ability to display data for multiple projects (gantt style) on one calendar, in layers

-sortable by milestone due date


I'm struggling with the best way to visualize this, because I want it all to fit on one 11x17 sheet. There are a large number of projects to show. We have a current spreadsheet listing all of our active projects, with milestones listed, but I can't figure out how to import the data in a useful way to this new chart.


I thought of using a variation of the planned vs actual chart, but I don't really need to be able to sort by planned or actual. Also, this chart displays weeks, not days. http://chandoo.org/wp/2009/06/16/gantt-charts-project-management/


My first attempt was below:

https://docs.google.com/spreadsheet/ccc?key=0AlT53niTsMvWdDZrd01LdDVMNlZxVUNYc3NBZEZmbFE

A document with a master calendar, and a second sheet sortable by due date.


If you have any ideas, or feedback on your experience with such things, let me know. For the record, I don't like Microsoft project or think it's visualizations are useful.

Any help/ideas would be most welcome! I'm an excel n00b, but I want to learn!

 

[Faseeh]

Hi justjazziness,


Welcome to the forum!


What about if we have a sheet that we can scroll to left, right and that have two row dedicated for each proejct, one showing actual and other idea. What i have understood is that you have a master sheet that sets target for each project and a weekly sheet that shows actual progress for each. is that correct??


Faseeh

 

[justjazziness]

Faseeh,

Thanks for the reply! Yes, I thought about doing a sheet as you suggest, but I don't know if that will fit one page. I also don't know how to handle different types of projects: some have multiple stages/milestones, and others just have one deliverable.


As to your understanding; I have one master sheet that sets target for each project (whether that's just one target for the project, or multiple targets, depending on the size and scope of work). The other weekly sheet is a mirror of that sheet, but is sortable by due date.


As you can see in the example I posted, I merely have an X for the project milestone and completion date. I want to show the duration of that task or stage, in gantt form.

Does that make sense?

 

[justjazziness]

UPDATE:

I modified this fantastic spreadsheet that does almost exactly what I want it to.

http://office.microsoft.com/en-us/templates/project-plan-gantt-chart-TC030009340.aspx


One problem: What is the best way to sort this calendar by due date?


Should I create a macro that allows you to sort by various items: due date, type of milestone, etc. Or, is it better to just highlight and use the sort option? I'm worried if I do that it will break the formatting, and look ugly.


Feedback? Ideas? If I am to create a macro, what are some good tutorials out there?

 

[Faseeh]

Hi justjazziness,


The templete you have mentioned is a really good one, if you click/select the cells D9:F9 on the Project Plan sheet and Filter it (Data > Filter) then i think you can sort and find which project is due by what date. I think that should work.


Faseeh

 

[justjazziness]

Faseeh,

Thanks for the message. When I try to filter as you suggested I get an error that "all merged cells must be identically sized to perform this operation". I tried unmerging but that breaks the spreadsheet. Also tried sorting instead of filtering, but it breaks the projects apart.


Is there a way to link the projects to the tasks and calendar projections of tasks so they always move as a group? I tried Data>group but this wasn't the sort of grouping I was looking for.


Thanks,

-Jasmine