Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zero values not legend, Value grater then zero show data and legend
My chart has about a dozen data series in it, with a legend created
for each. A couple of the data series currently are zero amounts, and my boss prefers not to show the legends for the zero amount data series in the chart legend. I understand how to delete the legend name from the chart legend and leave the data series as part of the chart. Is there some way to check each data series when the chart (or worksheet) is activated, see if the amount is no longer zero, and if so, reinsert the legend information for that data series in the chart legend? I know i can use pivot table but in order to create a pivot table the first row become static and I want the row to remain under formula because if the criteria does not fit the requirement I don't want to see it in the chart. Row Labels Sum of Jan. 2005 Sales Sum of Feb. 2005 sales Sum of March 2005 sales Paris 1801.76 1705.16 #N/A London 1620.55 1701.8 1403.61 Milan 885.31 779.69 809.13 I need the cities on the X and the sum of Jan. 2005 sales in the Y but if the city shows no sales in March don't show it. thank you, Pamela xoxo |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Zero values not legend, Value grater then zero show data and legend
If the chart is on a graph sheet, try :
Private Sub Chart_Activate() Dim s As Series, Ctr As Double With ActiveChart For i = 1 To .SeriesCollection.Count Ctr = 0 For Each v In .SeriesCollection(i).Values Ctr = v + Ctr Next v If Ctr = 0 Then .Legend.LegendEntries(i).Delete End If Next i End With End Sub HTH Daniel My chart has about a dozen data series in it, with a legend created for each. A couple of the data series currently are zero amounts, and my boss prefers not to show the legends for the zero amount data series in the chart legend. I understand how to delete the legend name from the chart legend and leave the data series as part of the chart. Is there some way to check each data series when the chart (or worksheet) is activated, see if the amount is no longer zero, and if so, reinsert the legend information for that data series in the chart legend? I know i can use pivot table but in order to create a pivot table the first row become static and I want the row to remain under formula because if the criteria does not fit the requirement I don't want to see it in the chart. Row Labels Sum of Jan. 2005 Sales Sum of Feb. 2005 sales Sum of March 2005 sales Paris 1801.76 1705.16 #N/A London 1620.55 1701.8 1403.61 Milan 885.31 779.69 809.13 I need the cities on the X and the sum of Jan. 2005 sales in the Y but if the city shows no sales in March don't show it. thank you, Pamela xoxo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I show a data table with legend keys in a line chart? | Charts and Charting in Excel | |||
Legend in x-y Scatter chart does not show marker | Charts and Charting in Excel | |||
Is Excel able to always show a legend like your help assistant? | Excel Discussion (Misc queries) | |||
Legend Needs to show numbers...HELP | Excel Discussion (Misc queries) | |||
Chart Legend Items: hide/show | Charts and Charting in Excel |