Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Location
    Pretoria, South Africa
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Charts (2003)

    Hi,

    I need to create +- 90 charts reflecting the same info just new people. (See attached file and chart)

    I attached an example. Row 1 is the constant and the rows below the variable data. I need a chart for each of the rows as in the example chart.

    My questions:

    1) Is there a way to use Excel to do this automatically? Or must I build a chart manually for each of the items?

    2) Can one set it up to ignore columns with no value?

    3) The charts must be Pie charts reflecting %)

    Thanks
    Thanks

    P

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

    Re: Multiple Charts (2003)

    Welcome to Woody's Lounge!

    Do you really need separate charts? In the attached version of your workbook, I have added a combo box from the Forms toolbar. The user can select a name from the combo box, and the chart will display the data for that name.

    I created some named ranges in Insert | Name | Define; the chart uses them, in combination with the contents of cells N2 and N3 (hidden behind the chart).

  3. #3
    New Lounger
    Join Date
    Jul 2007
    Location
    Pretoria, South Africa
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Charts (2003)

    Works well, having a Macro that creates the charts automatically will be best. Also I need to remove the 0% in the Pie.

    http://www.pcreview.co.uk/forums/thread-3162663.php

    The above link is sort of what I am looking for. My knowledge about Macro's and scripting, however, is limited. I have no idea how to edit the Macro to do what I want.

    Any help will be appreciated.

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

    Re: Multiple Charts (2003)

    I have attached your workbook with an adapted version of the macro from the thread you refer to. I have commented the code extensively.

    Suppressing the zero values would be a lot of extra work, I think.

  5. #5
    New Lounger
    Join Date
    Jul 2007
    Location
    Pretoria, South Africa
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Charts (2003)

    Hi,

    Found this on a site. Can this help?

    P
    Hiding Zeros

    People have trouble hiding data labels for wedges with zero values. The wedge isn't visible, but the label hangs in there. If the label is showing a simple value or percentage, you can use a custom number format that hides the zero. Double click the labels, click on the Number tab, then select Custom from the list, and use 0;;; for whole numbers, 0.0;;; for decimal numbers (with as many zeros after the decimal point as required), or 0%;;; or 0.0%;;; for percentages without and with decimal digits. A number format has four entries separated by semicolons: These are for positive numbers, negative numbers, zero values, and text. Since there is no entry for zero values, the data label will be present but it will contain no visible text.

    If the label contains more than just a numerical value, a custom number format will hide the zero, but the associated text will still appear. There are a number of ways to hide this text. One way is to use an autofilter on the chart's data range, and hide the rows with zero values. By default, charts do not show hidden data, so the wedge and its label do not appear in the chart. If the data changes, you have to update the autofilter.

    Another option is to create a custom label in a cell as described above. Modify the formula to something like this:

    =if(A2>0,A1&" ("&A2&")","")

    You can use a VBA procedure to hide your data labels. One drawback of this is that the labels have to be reapplied and the macro rerun whenever the data changes. Below is one simple macro that deletes any labels in the active chart if the associated value is zero. To add a macro to your workbook, Press Alt+F11 to open the VB Editor, then Alt+I and M to select Module from the Insert menu. Copy the code below and paste it into the code module. If the module says Option Explicit at the top when it is opened, delete the duplicate line. To run the macro, select the chart, press Alt+F8, select the macro CleanUpActiveChartLabels, and click Run.

    Option Explicit

    Sub CleanUpActiveChartLabels()
    Dim iPts As Integer
    Dim nPts As Integer
    Dim aVals As Variant
    Dim srs As Series
    If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation, _
    "No Chart Selected"
    Else
    For Each srs In ActiveChart.SeriesCollection
    With srs
    If .HasDataLabels Then
    nPts = .Points.Count
    aVals = .Values
    For iPts = 1 To nPts
    If aVals(iPts) = 0 Then
    .Points(iPts).HasDataLabel = False
    End If
    Next
    End If
    End With
    Next
    End If
    End Sub

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

    Re: Multiple Charts (2003)

    Do you only want to hide the data labels for values that are really 0, or also for non-zero values whose percentage is rounded to 0%? For example, if the percentage is 0,4823%, it will be displayed as 0%.

  7. #7
    New Lounger
    Join Date
    Jul 2007
    Location
    Pretoria, South Africa
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Charts (2003)

    The chart should just ignore fields in the data sheet that's got a value of 0 or nothing in it.

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

    Re: Multiple Charts (2003)

    Yes, I understand that that's what you want, but as I mentioned it is a lot of work.
    If you really need that, you'd have to do the following

    - Start at row 2.
    - Copy the non-blank/non-zero cells in this row, and the corresponding cells in row 1 (the category labels) to another location, for example somewhere to the right of the present data.
    - Create a chart based on the copied range.
    - Repeat these two steps for each following row, copying the cells to a location below the one for the previous row, until you've processed the last filled row.

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

    Re: Multiple Charts (2003)

    Here is a version that implements the ideas from my previous reply.
    In this version, you don't have to select the data, you only need to click the button.
    You'll still see 0% in the charts, these correspond to non-zero values that represent less than 0.5% of the total, so they are rounded to 0%.

Posting Permissions

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