jim.thornton
06-18-2009, 10:49 AM
I'm hoping someone can help me here. I'm uploading a screenshot of a chart and the Sheet data populating the chart. You will notice, in column "P" there is a list of months. This month is being extracted from the Data in column "A" (no in the screenshot).
I have set my chart up to use data all the way down to row 1000 (but there is only about 30 rows right now actually populated. I did this because I'm going to be having other people use this spreadsheet and I don't want them playing around with selecting data in fear that they will mess something up. In addition, I don't know of any other way to drive a chart other than to enter in the select range up to 1000 when creating the chart.
Anyway... Because I have a bunch of blank fields in the range, the chart will give a label to "blank", which I don't want. To get around this, I have used the filter and deselected ALL and deselected the "blank" option. This removed the label from the chart but it also made it so that if I add new data in and a row that hasn't been entered, that now I have to go back in to the filter and select the new month.
Is there a way that I can do this automatically? I have a refresh button beside the chart. I was thinking that I can add code into the refresh button that will do it for me (or the user). The refresh button executes the following macro:
Sub Refresh()
'
' Refresh Macro
' Refresh the statistics.
'
'
Range("D3").Select
ActiveSheet.PivotTables("Volume").PivotCache.Refresh
Range("G3").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("A1").Select
End Sub
Finally... You will notice in the chart that there are data labels to the right of the bar. In the data label, I have selected to show the "Series Name" and the "Value". I would like this to automatically be configured as well (including the formatting).
Any help is greatly appreciated.
I have set my chart up to use data all the way down to row 1000 (but there is only about 30 rows right now actually populated. I did this because I'm going to be having other people use this spreadsheet and I don't want them playing around with selecting data in fear that they will mess something up. In addition, I don't know of any other way to drive a chart other than to enter in the select range up to 1000 when creating the chart.
Anyway... Because I have a bunch of blank fields in the range, the chart will give a label to "blank", which I don't want. To get around this, I have used the filter and deselected ALL and deselected the "blank" option. This removed the label from the chart but it also made it so that if I add new data in and a row that hasn't been entered, that now I have to go back in to the filter and select the new month.
Is there a way that I can do this automatically? I have a refresh button beside the chart. I was thinking that I can add code into the refresh button that will do it for me (or the user). The refresh button executes the following macro:
Sub Refresh()
'
' Refresh Macro
' Refresh the statistics.
'
'
Range("D3").Select
ActiveSheet.PivotTables("Volume").PivotCache.Refresh
Range("G3").Select
ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh
Range("A1").Select
End Sub
Finally... You will notice in the chart that there are data labels to the right of the bar. In the data label, I have selected to show the "Series Name" and the "Value". I would like this to automatically be configured as well (including the formatting).
Any help is greatly appreciated.