[ms office - o365] Another Excel Puzzle

[featured_spectre]

Hi guys

 

So, I am helping my stepdad design a project management spreadsheet for his company (don't ask, got roped into it). So I have created the basic template, but I am struggling to fill in the "gannt chart" part of it that he wants to use.

 

I have attached the sheet.

 

Basically, I need it to check in the cells in the "gannt chart" if it is within the dates listed, if yes, match the colour of the cell the date is (Red for initial submission, orange for decision date etc etc)

 

Sample Gannt.xlsx

 

Would be much appreciated. My googlefu is lacking, and when I suggested using a bar graph etc, it was flatly shot down.

[featured_spectre]

Should add - progress will be manually input. not sure why, but hey how

[Seb1780]

You could conditionally format the rows - something along the lines of

If the date associated with a cell >= start date of stage
AND
If the date associated with a cell <= end date of a stage
THEN
Fill cell in with appropriate colour

[featured_spectre]

Yes but is there any way to apply that to the cells in that chart as it is essentially a calendar which can scroll (don't ask me why it was like that, I got told to work with it)

[Seb1780]

Yes, so long as the cells in row 8 & 9 do not scroll and just the date scrolls at the top, then the conditional formatting would update as the dates moved and the cells would change colour.

[featured_spectre]

Sorry if I am being dense, are you able to do an example? I can't get it going with the full colour set

[featured_spectre]

Hmm... I must be being daft, I can't get it to pick the 7 dates in the If format....annoyingly!

[Seb1780]

Not IF statements - you just need conditional formatting - seven different rules to colour according to the dates.

 

If I get chance I'll look again later, but I cannot promise.

[featured_spectre]

Would be amazing if you could Seb I am a bit daft when it comes to conditional formatting at the minute. I really ought to brush up on my excel skills!

[Seb1780]

OK - attached is a worked example

 

Col A is just labels showing five dummy stages of a project

Col B is start date and Col C is end date of each stage - the conditional format refers to these columns

 

Row 1 just has dates in sequence, just like your fancy scrolling calendar

 

In the range E2..BM6 there are 5 conditional formatting statements, one for each stage. I've seperated them out into rows to help you get the idea of what is going on, but this could easily be all in one row (for me, a Gantt chart is one row per project stage as I've set it out.)

 

HTH

 

For Featured_Spectre.xlsx