Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Line graph

  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am a complete novice when it comes to using charts. I'm trying to compile a line chart of 15 peoples (A-O) progress accross time of 6 dates. The amount of days between each plotted date is not relevant here.

    I only want to see 6 dates on the category axis, and the plots should be an equal distance apart. Also I would like to flip the value axis so that 0 is at top and 180 is at bottom. I want the plotting to start from 171, not 0. I need the value in increments of 5.

    Please could I get some pointers as to how to achieve this. Thanks

    [attachment=85596:Line_Chart.xls]
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='VegasNath' post='793977' date='19-Sep-2009 07:24']I am a complete novice when it comes to using charts. I'm trying to compile a line chart of 15 peoples (A-O) progress accross time of 6 dates. The amount of days between each plotted date is not relevant here.

    I only want to see 6 dates on the category axis, and the plots should be an equal distance apart. Also I would like to flip the value axis so that 0 is at top and 180 is at bottom. I want the plotting to start from 171, not 0. I need the value in increments of 5.

    Please could I get some pointers as to how to achieve this. Thanks

    [attachment=85596:Line_Chart.xls][/quote]
    I am bit confused what exactly you are looking for as I generally dont use charts but I tried something. Hope this will help to get some idea.
    Attached Files Attached Files
    Regards
    Prasad

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Prasad didn't explain how he modified the chart.

    Click on the chart to select it.
    Select Chart | Chart options...
    Activate the Axes tab.
    For the Category axis, select Category instead of Automatic (or Time scale).
    Click OK.

    Next, double click the value axis (or click on it, then select Format | Selected axis...)
    Tick the check box "Values in reverse order".
    If you still want the category axis to be displayed at the bottom, tick the check box "Value (Y) axis crosses at maximum value".
    Set the Major unit to 5, and the Maximum to 175 (the first multiple of 5 above 171).
    If you really want the axis to stop at 171, set Minimum to 0 and Maximum to 171.
    Click OK.

    Note: I don't think the values in column B belong in the data values for the series, but that's for you to decide.

    See attached version.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794005' date='19-Sep-2009 15:38']Prasad didn't explain how he modified the chart.[/quote]
    My mistake. Although I tried to adopt the same approach but I was not sure whether this will serve the purpose or not.
    Regards
    Prasad

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='prasad' post='794008' date='19-Sep-2009 12:23']My mistake. Although I tried to adopt the same approach but I was not sure whether this will serve the purpose or not.[/quote]
    You did take the correct approach as far as I can tell!

  6. #6
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='794010' date='19-Sep-2009 16:09']You did take the correct approach as far as I can tell![/quote]
    Thanks for encouragement.
    Regards
    Prasad

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I'm not familiar with using any charts, but probably could and should a lot more. I have been dabbling around and now have it the way that I want.

    Now I am trying to have it automatically update when the source data changes (expands), so I have a few questions:

    In the series there are 15 people:

    Name: ='Admin Chart'!$A$1:$B$1 TO ='Admin Chart'!$A$15:$B$15
    Values: ='Admin Chart'!$H$1:$M$1 TO ='Admin Chart'!$H$15:$M$15
    X axis labels: ='Admin Chart'!$H$17:$M$17

    The values and X axis labels need to increase by an additional column following this:

    amh.Range("H1:H" & w).Copy Destination:=ach.Range("N17") <> This needs to be the first blank cell on row 17, how?

    Once the data is pasted to the next available cell on row 17, there are lookup formulas in rows 1:15 of that column, which need to then be included in the chart.

    so the source data needs to expand to:

    Values: ='Admin Chart'!$H$1:$N$1 TO ='Admin Chart'!$H$15:$N$15
    X axis labels: ='Admin Chart'!$H$17:$N$17

    I'm hoping that this makes sense

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Change

    amh.Range("H1:H" & w).Copy Destination:=ach.Range("N17")

    to

    amh.Range("H1:H" & w).Copy Destination:=ach.Cells(17, ach.Columns.Count).End(xlToLeft).Offset(0, 1)

    ach.Cells(17, ach.Columns.Count) is the last cell in row 17; .End(xlToLeft) finds the last non-blank cell and .Offset(0, 1) is one cell to the right, i.e. the first blank cell.

    To make the chart expand (or contract) automatically, you need to define dynamic named ranges for the X values and for each of the series, and use those in the definition of the data series for the chart.

    See the attached version.
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV' post='794078 View Post
    To make the chart expand (or contract) automatically, you need to define dynamic named ranges for the X values and for each of the series, and use those in the definition of the data series for the chart.

    See the attached version.
    Sorry Hans, I don't understand the steps that you have taken to set this up. Please would you mind explaining?
    Also:
    I can see that this works when new data is added to the right of the existing data. But I have formulas there that will return zero until the raw data is added below. So how do I have the chart ignore the blanks?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Select Insert | Name | Define... to see how the dynamic named ranges are defined.

    Select the chart, then select Chart | Source Data and activate the Series tab. You'll see how the dynamic named ranges are used in the definition of the series.

    More info about dynamic ranges in How to create a dynamic defined range in an Excel worksheet. And about dynamic charts in Create a Dynamic Chart.

    I'd remove the formulas that return zeroes or blanks if there are no data. You're filling the worksheet using code anyway, so you can add the formulas when data are available.

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans, I'm making slow progress....

    In the attached version, what do I need to change to remove the blanks from the chart. I'm guessing that it is to do with the defined name, would you mind explaining the formula to me please?

    [attachment=85609:Line_Chart_4.xls]

    Also, the next data addition will be to cell N17. At that point I will need to add a formula to cells N1:N15 >

    =VLOOKUP($A1,$A$19:$M$189,13,0) - looking at cells A1:A15 respectively.

    Following that will be O17, still looking at A1:A15 but column 14 and so forth.

    How can I achieve this please?
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you remove the "x"s from A17:G17, the chart will be OK.

    If you add data to N1:N15 and N17, the chart will adjust itself automatically.

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV' post='794094 View Post
    If you add data to N1:N15 and N17, the chart will adjust itself automatically.
    I have sorted out the adding of data to N17 and below as thats a simple copy & paste. I still can't work out how to get the vlookup formulas into N1:N15 (and corresponding columns) that feed the chart, as per my previous post?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='VegasNath' post='794099' date='20-Sep-2009 00:59']I still can't work out how to get the vlookup formulas into N1:N15?[/quote]
    By entering them, either manually or through code, perhaps?

    I'm sorry, I don't really understand your problem, but it's bedtime for me now.

  15. #15
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, I think I'm close. Would you mind taking a look at the code in the attached file which is practically complete apart from the incorrect syntax for adding the VLookup formulas to the range. Hopefully the comments in the code will better explain what I am attempting to achieve. The formulas that I need in column N will be the same as are in column M apart from they will need to look to column 14 instead of 13. Hope this is clear.

    Thanks for your help, and enyoy your well deserved nap!

    [attachment=85611:Line_Chart_5.xls]
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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