+ Reply to Thread
Results 1 to 31 of 31

gantt chart in excel

  1. #1
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    helo
    i have name in one column and dates in rows. now i want to creat a gantt chart. My special problem is there are many same name(however in one order) in same column but i need only one name in chart. is it possible? for example:

    Name Resources jan feb march apr
    A X 1 2 1 1
    A y 1 1
    A z 1 1
    B z
    B z
    C y
    C
    C
    C

    in above table(while posting it could be possible that all data come near) though there are three A but these are one project so i need only one line in chart. and the duration is from jan to apr. However in second row company needs resource y only till feb. but the line should show duration from jan to apr.
    guy i m really in trouble.

    thanx

    birkhe

  2. #2
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: gantt chart in excel

    Birkhe,

    What you need to do is reorganize your source data so that you are able to make that gantt chart. There are some nice examples on youtube. Look for:
    "excel gantt chart"
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  3. #3
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    hi

    hi
    thanks for your reply. However main problem is each month there will be change in the order of names. and so for these duration.
    Isn't it possible to filter each name in that Name column and duration related to it?
    If some formula, or macro or etc. can help to fix it, then it would be nice .

    hoping a good solution.

    birkhe

  4. #4
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56
    duration
    name resource may june july aug
    A x 1 4 5 3
    A y 5 5
    A z 4 2 6
    B z 3 2
    B x 6 4
    C x 2 3 6
    C y 3 5 4
    D x 3 4
    D x 3 4 4
    D z 4 4 3 4


    the main problem is each month the order in Name column is changed. so using some formula i have to take only one name and duration maximum duration (e.g. for A from May to Aug and for B from ma yto june.
    so the formula should take Name and duration related to it in to consideration.


    thanks
    birkhe

  5. #5
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    my data type

    please look at the attachment.

    birkhe
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    It's not clear how your data would relate to a gantt chart.

    Take for example project B. There is data in May, June and July.
    Should the bar just cover those months?
    Should the 1,2 or 3 in May be displayed in some way?
    Cheers
    Andy
    www.andypope.info

  7. #7
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    details of my data

    hi there

    there in table you can see the names of the project. three times projet A , three times project B etc.
    The duration of the project is in rows that you see like may june july. Yes the chart should cover these months.
    If you open the attached excel file you can see the three project A with some resources needed (X,Y,Z in different months) however the gantt chart should show only one line for Project A, not three lines ( now i have three in normal gantt chart).And the duration should show those month when it got resources (X,Y;Z etc).
    One fact is that project A may need resource X in may, June and July ( these 1,2,3 are lets say people)
    again project A may need resource Y only in May and june.
    So the duration of the project is from may to july. Though it needs resource Y only till june.

    Now the gant chart should show only one line for project A and duration from may to july.
    Each month the location of project names changes. so the chart should take name and duration related to it in to consideration.
    is it possible somehow?

    if yes that would be great.

    birkhe

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    You will need to create a summary table.

    See attached, which includes extra series plotted as columns to holder month names.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    thanks Andy
    you have given me a formula in which result is only 1 (thats the nuber in each cell) and in some places N/A. If the order of project name i.e. A,B,C changes does it affect the chart? or the formula can identify from the cells the name of projects?
    another question is, can i simply copy and paste the formula? Actually i have 600 items in column and around 20 months in rows like i gave in the example.
    And each month, the order of the project name and this duration may be changed. so the formula should be some kind of dynamic which can identify the name of project and duration related to it.


    cheers
    birkhe

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    The formula can be copied and pasted. Obviously you will need to change the absolute reference to the source data.

    You will have to know the Project names and months in advance. This is only semi automatic.

    The formula returns a 1 if any data is found for that project in that period.
    N/A is returned is no information is found. The N/A will stop data markers from being displayed.

    The order of the projects within the source data is not important.

  11. #11
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    hi Andy
    thanks for your reply. Now the question is is n't it possible to make summery of project names?
    only one name from the stack of names from particular column.
    if yes please.

    Birkhe

  12. #12
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56
    i changed the formula in following way. But its not working. Sorry for putting alot of question.


    =IF(SUMPRODUCT(($A$9:$A$593=$BO$9)*($R$8:$AW$8=$BP$8)*($R$9:$AW$593))>0;1;NA())



    birkhe

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    What exactly do you mean by "But its not working"? Error, wrong numbers...

    Search the forum for formula to return unique items from list.

  14. #14
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    excel chart

    hi Andy
    Actually formula is working but if copy and paste because of $ sign the cells are not changing. for example if i write this formula

    =IF(SUMPRODUCT(($A$9:$A$593=$BO$9)*($R$8:$AW$8=BP$8)*($R$9:$AW$593))>0;1;NA())

    and if copy for all cells then it will not take relevant cells in to account. It only takes rows in to account but not the column. If i copy downward it remains same.
    if i change the relevant cells manually then it works.

    another problem is in graph. i can not show dates in X axis. You have created secondary axis somehow there which i could not under stand. When i created graph then i can show only project names(Y axis) but can not show the months (X axis).

    birkhe

  15. #15
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    hi Andy
    i am sorry that i am almost making you do my job.
    you can find in the attachment a table and a chart. actually the chart is independent of that table. i just want to explain that the graph should show the starting date and end date.
    For example project A starts from May however project D starts from somewhere in august. so line for project D should start from Aug not from may. I hope you understand my problem.

    hoping the solution of both queries( previous post as well)

    birkhe
    Attached Files Attached Files

  16. #16
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    Firstly the formula problem.

    If you look at my example you will see I used a mixture of relative and absolute references. Your formula is all Absolute references, so once the formula in the top left corner of the output region is set it will not copy across or down properly.


    The chart and data source in your latest example uses proper dates.
    How can that information be obtained from your resources table. You only record Month category and persons used.

  17. #17
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56
    hi Andy
    please check my excel file that i have entered the cells in order that you have provided. i did not get months there and each line starts from biggining though the start date are different.

    cheers
    birkhe
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    hi andy
    i got this formula error and i corrected it. now its working.
    As you replied that my date uses specific dates so isn't it possible to take these specific dates in duration axis? I mean taking that series.

    For graph (chart), all lines start from very biggining. as i already wrote it should start from the project biggining date. Now it seems all projects have same starting date though when we point, t shows the exact date. but it makes the viewer confuse na?
    so if it is possible i will solve it with your help.
    I am making a lot of debate because it is a unique problem i have ever faced.

    birkhe

  19. #19
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    Taking your latest chart example you will need to add an aditional column to the data that acts as a padding bar to move the start point for those projects that do not being in May08.

    So create a stacked bar, plot by columns, on range B3:V7.

    Format all series to have same fill colour.
    Format Padding series to have no fill colour. I have used a pattern fill for the example.

    Use Source Data to add new series, called Dates.
    Select new series and change chart type to Colulmn
    Use Source data again to set the values to C2:V2
    set Category values to C3:V3
    Remove primary value axis labels
    Set secondary category labels to low
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    hi Andy
    i can not open the file. can you resend that?

    regards
    birkhe

  21. #21
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    I just tried the posted file and can open okay.

    What does it say when you open the file?

  22. #22
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    thanks Andy
    i got it. thaks a lot. i will apply it to my data and let you know either it is solved case or not.
    thaks once again for your continious help.

    birkhe

  23. #23
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    hi Andy
    i applied all the process but something is missing so i can not get the same type of chart that you sent me.
    can you please explain the process from the very biggining? I started with paddle and then with each month finally with series date. I applied no color but the thigs are not appering what they should be. my chart is a mess. so please can you explain the process serially.

    regards
    birkhe

  24. #24
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    I did miss 1 step which is to set the maximum value of the primary value axis to 20, number of months.

    Attached contains step by step instructions and charts
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    hi Andy
    almost solved. In last part i could not understand to change from primary to secondary axis. Now i have project name in X axis and duration in y. only to set from primary to secondary axis.( i do not know why i need that)
    I just wanted to ask you that if i have to extend the data does this gant chart changes automatically? or each time i have to change the data range? because i have to add one month forward and delete one previous month. so that it works continiously.
    For example now its May and the last date is december. after one month i want to delete may from the chart and add jan ( for next year) in gant chart.
    i think i have to adjust in formula and chart. what do you suggest?

    thanks a ton

    birkhe

  26. #26
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    Because you have to fix the scales max value the chart will not automatically update.
    You will need to change the data ranges and the limits of the scale.

  27. #27
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    thanks Andy
    but my first question was how to change primary axis to secondary (the last part that you sent me). does it affect the chart?
    but how to change it?
    thanks

    birkhe

  28. #28
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    To change a series to the secondary axis,
    select series
    right click and pick Format Data Series
    Axis tab
    Plot on: Secondary axis

  29. #29
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    Hi Andy
    for now problem solved. thanks a million.
    if i have further question i will write to you.
    because i am raw in Excel.

    thanks once again.

    birkhe

  30. #30
    Registered User
    Join Date
    10-19-2008
    Location
    Germany
    Posts
    56

    gantt chart in excel

    hi Andy
    Everything is working when i create chart in the same sheet but if i create chart in another sheet then it shows only NA in each cell.
    So can we create in another sheet?

    birkhe

  31. #31
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419
    If the data the chart is based on is not showing N/A then the chart should not either.

    Check, using the Source Data dialog, that the chart is using the correct ranges.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automate percentage complete in an Excel Gantt chart
    By brndtaylor in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 02-04-2013, 10:00 PM
  2. Exporting Word Chart to Excel (Resizing Chart)
    By bg18461 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2008, 11:49 AM
  3. emulating this chart in excel
    By dtevol in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-16-2008, 05:34 AM
  4. Gantt Chart Automate
    By brndtaylor in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-22-2007, 09:12 PM
  5. formatting excel chart
    By excelguru in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-03-2007, 09:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1