GANTT chart based on conditional formatting - macros to merge cells

[Florinnn]

Hi guys!

Have to warn you, this might be quite challenging.

I have designed a Gantt chart that populates automatically based on several conditions. The cells represent weeks and each of them will be coloured differently depending on a phase which they belong to and a code which they are assigned with. What i need is to merge them automatically, have a box around them, and display the phase name if they belong to the same phase and the same country.

Obviously, I have attached some sample data otherwise it would be impossible to understand what I mean.

Many many thanks in advance.

Florin

P.S.: Is this even possible even? Do let me know.

[Marcol]

Hi

Just opened your file in 2003 - formulae truncated and other issues in conversion.

First reaction on seeing the result - There must be an easier way!

Could be wrong - I'll have another look when I get to a 2007 machine, unless someone else takes up the challenge.

[Florinnn]

Well, yeah, forgot to mention. It won't work in prior versions to 2007
There must be an easier way to do what? Merge the cells or do the gantt? Or both? Any suggestion will do.

[Marcol]

No offence intended.

I usually do Gantts in M.S. Project

I've fired up a 2007 machine and will have another look

Would there be any advantage in making it work in both 2007 & 2003?

EDIT:- Okay I've worked out your nested IF Formulae

1/. What are your rules for colouring the cells? (Columns "AG" >)
2/. What defines the Phases -- PDP, A+D, Build, Test, Release? (Okay I've got it, How do we handle the overlap between each phase?)
3/. Do you need the RAG number in the background? Does it serve some other function later on?

Think I understand the rest.

Don't think we need Labels/Textboxes they are a bit of a pain when printing

Cheers

[Florinnn]

Hi Marcol,

Sorry for the late reply. Was away from the computer over the weekend.
Let me try and answer your questions:

Rules for colouring the cells are based on the RAG status. The number is not neededin the background, but I don't know how else I could get that colouring (as in displaying multiple phases on the same line, something that isn't really possible in MS Project). As a reminder, this chart needs to be generated by simply putting in the dates and the RAG status.

The text boxes are not needed (and are not even desired). I just put them on to display what exactly I had in mind. And in theory there should be no overlap between the phases (other than the one day which can be allocated to either, doesn't matter, preferably to the phase it is the start date of).

Does this answer the question? Any idea if it can be done?

BIG THANKS!
Florin

[Marcol]

No problem Florin

That will make life a great deal simpler!

I'm away from my 2007 machine where I have your file, look back late this evening I may well have something for you by then.

Regards
Alistair

[Florinnn]

That'd be absolutely amazing. Many many thanks.

[Marcol]

Sorry for the delay, ran into problems with the number of possible date combinations!

Please have a look at the attached workbook.

Don't worry about the colours I have used, if you could mock up a sheet with the colours you want I'll adjust the code to suit.
At present the colours are from the standard palletes, might have to use RGB so I don't want to improve them until you tell me your preferences.

Once I know what you want I can add an option to change them. (Don't know enough about theme colours, but if they are avoided the sheet will work in 2003)

The overlaps are deliberate thought an indication of this situation would be handy.

I have a few more ideas for extras so hang in there!

Cheers

[Florinnn]

WOW!
Ok Had a look at the code an am all confused. Will need some time to decipher.

Let me try to answer some of your questions:
Colours: 1 for Green, 2 for Amber (darkish yellow preferably) and 3 for Red (forget anything else there was in the data before).
Also, rather than having A+D, could you have the boxes display "Analyze" or "Design" specifically? And could you rename PDP to "Define"? I somehow uploaded somewhat older data and didn't realize. Also, could another category be added saying "Other: (with the same 3 sub-columns Start, End, Rag). I was thinking I could make those updates myself but now looking at it I am a bit confused. Also, the overlap is actually not needed (I don't think).

MAIN POINT: the colours should NOT be according to PDP/ Analyze etc, but according to RAG status. As in not all PDP boxes should have the same colour. RAG stands for RED, AMBER, GREEN ...to make it clearer.

Do you think this is doable still? Maaan...I owe you at least a dozen pints! This is amazing work already and I am all impressed.

And another question: Can it be done as one Sub with functions? So I can just press a button that does the whole thing?

Many many many many thanks!

[Marcol]

No real problem

1/. I was going to ask what RAG was! Now I know it's obvious!
Changing colours is no problem.

2/. Changing phase names is no problem.

3/. The routine does show "Analysis" and "Design", but only if you have dates in the respective columns!
If there is only one set of dates then the caption "A+D is displayed

4/. I have allowed for adding extra rows. Adding extra columns is a different ball game.
Where would they fit in a logical progression of phases?
I suspect by the description "Others" they would randomly overwrite the established progression.
Would this not be better in a second row in each "bussiness unit" if required?
I'll demo this later.

5/. The chart updates as you go, change or add a date and its done!
I don't think adding a button would be any advantage, only adds another complication.

6/. The code as it stands is just my rantings.
I'll tidy it up as I go and add more comments where they are needed to help you understand it better.

Don't despair - the troups have it under control !!!!! I think?????? ..

Regards
Alistair

[Florinnn]

Hi Alistair,

You are completely on the right track. I would guess "Other" would go at the very end in case we are talking about columns (so the data could be simply put under Other)...If you have a better idea, feel free. Your suggestions have been absolutely amazing.

"A+D" is extremely cleverly done. Wouldn't have even dared to do ask for that.

Also, the number of markets/business units will be significantly higher. Can you make sure I can keep on adding rows at the end?
EDIT: Noticed it does that already if rows are inserted. However, data will be just copy pasted to the left, so can you make sure that the formatting applies further down, so there is no need to actually manually insert rows (if that's not too much to ask).

And I have noticed that if I insert a new row with dates, the grouping doesn't happen.

Thanks again! Brilliant work.

[Marcol]

Up to 1,048,576 in 2007 or 65,536 in 2003 if your machine can handle it!

[Marcol]

Hi Florin

Sorry about the delay in posting this version of the Gannt Chart.

1/. WeekNos begin 1/1/2010 and involve a "cheat" week 53 is combined in week 52 as this is probably a holliday period it should be ok.
Be careful with week numbers some countries use a different criteria and in some the week starts Sunday.

2/. I have not added an extra set of columns for "others".
Instead I have allowed for this when adding an extra row. I think this is probably a more versitile way of handling "others", bearing in mind that this would be a minority case.
You have the option to add up to 4 phases in this row as a pose to 5 in standard rows.

I think I have covered all of your main points, but this will need fully testing, there are bound to be bugs, users come up with all sorts of key combinations and they can cause problems.
Record carefully any you find.

Have fun!

EDIT:

Attached file updated to work with 2003

[Marcol]

Hi Florin

This thread should really be ended, however, the new file falls broadly in the scope of the original question so here it is.

1/. I have amended the chart to cover 2010>2012. (2012 displayed in Qtrs).

2/. I have commented out the cell comments option.

3/. If you change your Phase names in C5 to R5 the code will read the names from there.
As yet it will only update when you next refresh a data row.

4/. Other changes you have made to the last book are obviously not in this new file, you will have to see to that yourself.

5/. From your PM

Since data will be copied from a Pivot Table in blocks, is there a way to refresh the Gantt without going into any row and pressing enter?

This is really another question all together, it isn't in the originating post, I had not unreasonably, assumed manual entry.
You will be better served posting an example of your problem in a new thread.

I'llI look out for it and will help if I can.

Have fun.
Alistair