Creating a Continous Program Cycle Heat Map

[JohnSlider]

Alright. So my supervisor has an idea that he would like me to implement, but it is so complex that I'm not sure how to do it. I already know that I cannot do it the way he wants in Excel because the program simply can't accommodate what he wants to do. However, I am hoping the experts here can offer some suggestions for a decent, Excel-capable alternative.

We have program managers in our office that have multiple projects they are managing. His idea is to list the program managers and their programs in a table that has a continuous timeline. There would also be columns for key project milestones. He wants to be able to put in a target completion date in each milestone column and have the time estimated to complete that milestone represented on the timeline in the form of a heat map. So, let's say one of the milestones is to complete quality testing. Say we have quality testing averaging 14 business days to complete. He wants to be able to scroll-down the timeline and see that milestone represented as a gradient white-to-orange-to-red-to-orange-to-white zone during those 14 days across the corresponding cells in the timeline.

Any ideas? I am at a loss.

 

[JohnSlider]

Something like this would work, I think, but I can't figure out how to go about A) creating the chart, or B) how to have Excel pull information from the columns to populate the chart.

 

[Steve=True]

Hi John,

Your question is difficult to help with as we don't have the data.

Here are a few alternatives that may help.
how-to-make-an-excel-project-status-spectrum-chart

new-take-on-the-excel-project-status-spectrum-chart

If you can post more data that might help.

Steve=True

 

[JohnSlider]

Here is what I have come up with:

I use conditional formatting on the schedule. Each cell has a function like this:

=IF(E3="Q1 of 2016","Sourcing",IF(F3="Q1 of 2016","LL",IF(G3="Q1 of 2016","PPAP",IF(H3="Q1 of 2016","SOP",""))))

So each cell is checking to see if any of the milestones has that quarter selected. If it does, then it puts a specific text into the cell and then conditional formatting makes the red gradient. If none of the milestones has that quarter selected, then conditional formatting gives it a green gradient. I use a drop down list for all of the milestones to prevent typos and to ensure that the cells can accurately detect when that quarter has been selected for a milestone.

Now what I want to do is implement a start date, and for the cell (quarter) before that start date to turn to a black gradient. To take it a step further, I also want all of the previous cells in the timeline to turn a black gradient. Similar to this:

Any ideas?

 

[JohnSlider]

How about this:

Would it be possible to reference the dropdown list? That way I could say in each cell, if the value in D3 is less than (before) item 2 on the list (Q2 2016), THEN enter "X"? Then I could set it up that way. Thoughts on a way to do this?

 

[JohnSlider]

OR what if I could assign a numerical value to each quarter so Q1 of 2016 = 1, Q2 of 2016 = 2, etc. etc. Then I could easily do, if the value in D3 is less than Q2 of 2016 (2) then enter "X"? Is that possible?

Sorry for the triple posting. I can't figure out how to edit posts here and I'm just trying to brainstorm.

 

[JohnSlider]

Nevermind! I got it! I created a new column and used this formula:

=if(D3="Q1 of 2016","1",if(D3="Q2 of 2016","2",IF(D3="Q3 of 2016","3",IF(D3="Q4 of 2016","4",IF(D3="Q1 of 2017","5",IF(D3="Q2 of 2017","6",IF(D3="Q3 of 2017","7",IF(D3="Q4 of 2017","8",IF(D3="Q1 of 2018","9",IF(D3="Q2 of 2018","10",IF(D3="Q3 of 2018","11",IF(D3="Q4 of 2018","12",IF(D3="Q1 of 2019","13",IF(D3="Q2 of 2019","14",IF(D3="Q3 of 2019","15",IF(D3="Q4 of 2019","16",IF(D3="Q1 of 2020","17",IF(D3="Q2 of 2020","18",IF(D3="Q3 of 2020","19",IF(D3="Q4 of 2020","20",""))))))))))))))))))))

To assign values based on the start date, then I will use this column to populate my heatmap.