Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Organising chart labels (WinXP, XL2003)

    This is going to be a bit complicated to explain, but I can't think of another way to ask for the info I need! So ...

    I've inherited a workbook which was created for us by a consultant, and includes a chart that is dynamically created from a table of data; said table is updated by the users each week/month/whatever. The table layout stays the same, ie around 10 rows where the names and descriptions stay the same, and only the values change, but when the chart is updated the changing figures mean that the order in which the lines on the chart appear is different each time; eg row 4 might have the hghest figure one period but row 6 the following, etc.

    Next to the chart is a column of descriptions which are again dynamically created from earlier data and use long formulae to do it; and this is the problem. The users currently have to cut and paste the contents of these cells to get them in the same order as the lines on the chart, and then fiddle with the row heights to get them to appear roughly in line - with indifferent success, as you can see by the attached screenshot.

    What I've been asked to do is (1) as a basic workaround, get these to be listed in the right order automatically, so they don't have to cut and paste to get them there, or (preferably) (2) find a way to put these descriptions automatically against the end of the appropriate line on the chart so they don't have to fiddle at all, not even to adjust the row heights as they do now.

    I've a feeling 2 will be impossible, although I don't know XL2003 well enough to say for certain, but I would much appreciate some help/ideas on how to do the first - which I'm certain IS possible, if not yet within my capabilities!!

    Any help would be much appreciated!
    Beryl M


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

    Re: Organising chart labels (WinXP, XL2003)

    Wouldn't it be easier to use a legend in the chart?

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Organising chart labels (WinXP, XL2003)

    I don't know - can you get the legend text for each line to be at the end of that line?
    Beryl M


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

    Re: Organising chart labels (WinXP, XL2003)

    No, but the legend items will have little coloured lines that enable you to match the chart lines with the legend text.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Organising chart labels (WinXP, XL2003)

    Beryl,
    Can you post a sample?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Organising chart labels (WinXP, XL2003)

    Unfortunately I couldn't persuade them that that would be good enough - they have to be at the ends of the lines. Hence the requirement to be able to reproduce these descriptions in the same order as the current results on the chart.

    Rory, unfortunately no, I can't paste any more than the screenshot I've put up already because it's all confidential stuff! Sorry!
    Beryl M


  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Organising chart labels (WinXP, XL2003)

    Beryl,
    Closest I can think of is to use Data Labels attached to the last data point - see attached sample. You may end up with issues where some overlap a bit but you can always move them manually if necessary. Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Lounger
    Join Date
    May 2001
    Location
    the Netherlands
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Organising chart labels (WinXP, XL2003)

    (Edited by HansV to make URLs clickable - see <!help=19>Help 19<!/help>)

    Try this page: http://peltiertech.com/Excel/Charts/LabelLastPoint.html
    or the supper add-in ChartLabeller at: http://www.appspro.com/Utilities/ChartLabeler.htm

    HTH

    Ingrid

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Organising chart labels (WinXP, XL2003)

    Thanks Ingrid - the second is no use because I'm not allowed to download/add anything to the system here, but the first looks good.

    The only thing is when I run it it puts the labels *inside* the chart (ie over the lines) not off the end, as shown on the demo, and I can't work out how to make the chart extend and put the labels off the ends of the lines!

    Do you know how to do this, please?
    Beryl M


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

    Re: Organising chart labels (WinXP, XL2003)

    The attached is a combination of Rory's chart and Jon Peltier's code. Click the command button to create or update the data labels.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Organising chart labels (WinXP, XL2003)

    Hi Hans, I'm back on this one again!

    I've been working on your code (in <!post=post 515871,515871>post 515871<!/post>) and I'm having two problems in particular - first, I can't quite work out how to get it to look at the correct data for the series (plural). Your code only seems to run on one series and ignores the rest. I think it's something to do with the fact that the series for this chart are assigned individually, not as a block?

    Second, the labels come in far too big - they need to be Arial 8pt, how do I add this to the code, please?

    By the way, is there any way of getting undo to work on things done by the code? At the moment I'm having to save the spreadsheet immediately before running it because I can't work out how to put it back as it was if the code doesn't work as I expect!

    Many thanks as usual!
    Beryl M


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

    Re: Organising chart labels (WinXP, XL2003)

    1) The code does loop through all series - at least, when I run it.
    2) See modified version (attached) for a way to retrieve the labels from a range of cells, and to set the font to Arial 8 point.
    3) Unfortunately, executing VBA code clears the Undo buffer in Excel. There are methods to provide some kind of undo, but they are extremely clunky.

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Organising chart labels (WinXP, XL2003)

    Hans, I'm beginning to see the light on this one now (sorry, having finally fixed the other complicated one, with your help, I'm back on this old chestnut!) - the trick to getting enough space to show the labels seems to be to have a column or two of blank values on the end of the series range - at least that's what you've done with your example.

    With a bit of playing I've determined I'm going to need about three blank columns to have enough space for the labels, but the series range is set as a named one. I haven't gone deeply into this workbook's formulae and code to see how the range is defined each time (it is *enormous* and extraordinarily complicated!) so rather than try to redefine the range, is it possible to tell the chart to use the named range plus three columns to the right?!

    I know I'm asking a lot, but you'd have to see this workbook to understand; it's an entire application in itself!
    Beryl M


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

    Re: Organising chart labels (WinXP, XL2003)

    Say that you have a range named MyRange. The range plus three columns to the right is

    Range("MyRange").Resize(ColumnSize:=Range("MyRange ").Columns.Count + 3)

    or

    Dim rng As Range
    Set rng = Range("MyRange")
    Set rng = rng.Resize(ColumnSize:=rng.Columns.Count + 3)

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Organising chart labels (WinXP, XL2003)

    Hi Hans, thanks for this, but it doesn't seem to be working? I've put these three lines in my code:

    MsgBox Range("Graph_Asset_2").AddressLocal
    Range("Graph_Asset_2") = Range("Graph_Asset_2").Resize(ColumnSize:=Range("G raph_Asset_2").Columns.Count + 5)
    MsgBox Range("Graph_Asset_2").AddressLocal

    Which shows the current range address in a msgbox, then resizes the range, then displays the address again - but the two addresses are identical? it doesn't seem to be changing it?

    It doesn't throw up an error either, just appears to do as I ask, but I noticed that the set of ranges used in this chart, including Graph_Asset_2 above, do not show in the list of available ranges that drops down from the "A1" box, far left of the toolbar above the sheet (sorry, I don't know what it's called!). Does this make a difference?
    Beryl M


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
  •