Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Gaant chart

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi.

    I am trying to create a cool tool to create Gaant charts with minimum effort. I have seen an example of this from J.Walkenbach's excel bible, but I want to extend this with your help!

    Esentially, I just want the user to specify the following:

    Type of Job, Week starting (number or date or both), duration (weeks or days or both), who is assigned to do the job (initials).

    I want to end up with:

    week number and dates across the top as columns,

    type of job listed down the side,

    the people's initials printed on the center of the bars in the chart.

    To take it even further, I would like to get an extra column, where one would specify % of work completed. For instance, for job no1 if the project is 80% complete, the user would type 80% in a column and 80% of the bar would be shaded in.

    Not as easy as you may think....

    RET79

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    http://www.j-walk.com/ss/excel/files/general.htm

    is a link that will take you to downloadz for some cool Gaant charts he has available.

    In addition to this I need:

    1. some way of putting employees initials on each BAR to show it is them in charge of that particular task

    2. some sort of input whereby you specify how much of the task is completed and the bar will be shaded accordingly.

    RET79




  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You may find something that helps on Jon Peltier's website. Jon is *the* Excel chart master, although MrExcel himself seems to be able to give him a run for his money.

    http://www.geocities.com/jonpeltier/index.html

    Tushar Mehta's site has a Gantt chart example which may be of use.

    Neither has the specific answers for you, but you might learn how to do it with their examples.

    Bye,
    Jay

    P.S. How did the calculus file work for you?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay,

    I just had a brief look at the calculus thing and it looks AWESOME. I do not pretend to understand how the coding all works but it has certainly opened my mind to what excel can do.

    WOW!!!

    RET79

    PS Thanks for the Gaant tip

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



























    Job Initials Amount done Amount left
    A RET 3380 845
    B RET/RB 4121 1232
    C JPS/RET 825 1600
    D ECM 5045 1433

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, this is my basic problem.

    With the table above, select it all, go to chart wizard, choose bar, then choose the one to the right of the default one (stacked bar).

    Click next.

    Now, what I get is the data is ok, but, both the ABCDE and the Initials are on the vertical axis.

    What I want is for the A,B,C,D,E to stay on the axis, but for the initials RET, RET/RTB etc. to be put ON the bar!!

    Now, I have ALMOST got this, if you go to step 3 of the wizard, click the data labels tab and then click the 'show labels' option button then it will put ABCDE, RET RET/RB etc. ON the bars like I want. BUT what I want is for JUST the initials on the bar and just the ABCDE on the vertical axis !!!!

    I hope I am making any sense, any help appreciated so much...

    RET79

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not that experienced with the finer points of Excel charting, but here is my offering...

    1. Interchange the Job and Initials columns, and only use the last three columns for your source data. You'll have the initials alone on the Y-axis and your data remains as before.

    2. Create a text box on the resulting chart and place it in the area of the first bar. Format as desired.

    3. In the formula bar at the top, type an = sign and then click on the cell that says "RET"

    4. Repeat for the other bars.

    Bye,
    Jay

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay,

    That's great, thanks.

    RET79

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyway, I am going to approach this thing in a different way now, ignoring the charts, and just putting the thing on a spreadsheet as it is currently.

    Jay, if you're interested I could send you this small spreadsheet showing what I want to get automated. You may/may not agree that doing a macro to create it would be better than charting the thing.

    RET79

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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