Automate Gantt Chart Creation

[Eclipse]

Good morning,

I am trying to find a way to automate this crude gant chart.

What I am trying to do is the following:

1: By entering a start date and due date, and by adding a percentage to 4 different goals, create a sort of gant chart for each job.

2: Have this chart update as the start and due dates change.

3: Be able to past other gant charts below this one, based on the leader assigned to each job. ie. if Dave is a leader, create a worksheet that will have all of dave's Gant charts in one workbook, on one worksheet, pasted each one below the next. The goal being we can give each leader 1 sheet that has all their jobs.

I know this is alot, so any help, or other suggestions are definately welcome.

Please see example workbook for general goal.

Thanks in advance,

E

[Andy Pope]

This might be a way to go.
Using vlookup and conditional formatting

[Eclipse]

Hi Andy,
Thanks so much for your reply. Can you please help me understand this a bit?

=IF(MATCH($A11,$A$11:$A$14,0)=VLOOKUP(B$10,$AS$2:$BE$6,13,TRUE),-1,0)

and why the conditional formatting would be simply equals -1 ?

I mean .. I know it all works .. I would just like to know how LOL.

Also .. in my cells AS3:AS6 I have Department due dates, and next to them is percentages.

Is there a way to take the Y5:Y6 dates, and apply a percentage of the dates to each department?

Thanks for your help!

E

[Andy Pope]

The formula checks the value in the 13th column of the table in $AS$2:$BE$6 and matches to the position of the Row label.

So matches on dates AND role report a -1 otherwise 0.
The reason I chose -1 and 0 is that they are easy to hide by using a custom number format. General;;;

If you set the number format to general you will see the results of the formula.

The latest file has a very crude allocation formula for the % of duration.

[Eclipse]

Thanks for the explaination Andy. I was wondering what the 13 was refering to, and now I under stand why you added the numbers in column BE.

Now one last thing (I think)

In the cases where there would be more or less days, How do I get the dates in row 10 to auto fill to the correct number of days, and then also the formulas in rows 11,12,13,14 to populate to the same?

And would this be able to change as the dates in Y5 and Y6 change?

Thanks alot!

E

[Andy Pope]

In C10 use

=If($Y$5+COLUMN()-2>$Y$6,"",$Y$5+COLUMN()-2) 

Copy across to BX10.

In B10 use

=If(B$10="","",If(MATCH($A11,$A$11:$A$14,0)=VLOOKUP(B$10,$AS$2:$BE$6,13,True),-1,0)) 

The copy these down and across B11:BX14

[Eclipse]

Outstanding! It works a treat!

Thanks very much Andy! I learned a lot on this one!


E