Pivot charts & tables

G

Graeme at Raptup

Hi,
I'm using Excel 2002 and have a number of charts that I want to put
together. I have 3 questions relating to pivot tables & charts.
(1) I wish to use the same file but each month add new data to the source
data. Will my pivot tables automatically incorporate new data? At the moment
I have all my data in one worksheet. I assume the easiest method of doing
this is to add new data below the last record?
(2) I have a number of charts where I want only the top 10 series. For
example I have a pie chart with the top 10 franchises (there are over 30). I
have one other pie 'slice' that is 'other' that makes up 100%. I have done
this by creating a separate table under the pivot table that references the
top 10 using an index lookup function. That works fine but I'd rather the
chart were a pivot table so that users can manipulate the data on the chart
(rather than on the table). I know that I can sort by the top 10 in the table
but this changes the data?!
(3)Is there a way of keeping colours of charts for specific characters
consistent? For example, lets say a franchise is called Taz, how can I ensure
that the pie slice for Taz is always in red?

Thanks for any help or suggestions!
 
J

Jon Peltier

1. Define a dynamic range on the pivot data worksheet, something like this:

Insert menu > Names > Define
Name: PivotData
Refers To:
=OFFSET(PivotDataSheet!$A$1,0,0,COUNTA(PivotDataSheet!$A:$A),COUNTA(PivotDataSheet!$1:$1))

In the pivot table wizard, when defining the source range, use
PivotDataSheet!PivotData, and whenever you refresh a pivot table based on
this dynamic range, the table will use the updated range.

dumping new data below the old is fine.

2. "but this changes the data" Yes, so you're always looking at the top ten.
Offhand, without a lot of experimenting, I don't know how to get the Other
category in the pivot table. Outside, of course, it's easy.

3. See this link:

http://peltiertech.com/WordPress/2008/03/03/vba-conditional-formatting-of-charts-by-category-label/

- Jon
 
G

Graeme at Raptup

Thanks Jon,
that's a great help.
Have not tested yet but am sure I can follow what you've suggested
 
G

Graeme at Raptup

Hi Jon,
re point (3):
I added the vba text as a module and get a run time error;
"Object variable or With block variable not set"

I've placed the chart in a separate worksheet, if that makes a difference.

Not quite sure where I've gone wrong here!?
 
J

Jon Peltier

There's no error proofing in that procedure. The procedure assumes that the
chart is embedded in the same sheet as the pattern table, and that it's
selected. You could of course reference objects more carefully:

Sub ColorByCategoryLabel()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("Sheet1").Range("A1:A4")
With Worksheets("Sheet2").ChartObjects(1).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex =
rcategory.Interior.ColorIndex
Next
End With
End Sub

I'm not sure rPatterns.Find requires that rPatterns be on the active sheet,
but you'll tell us, won't you?? <g>

- Jon
 
G

Graeme at Raptup

Mmm,
I get an error "Subscript out of range"

By the way I tried placing the chart on the same worksheet.
Initially it did not work until I realised that the cells in the range were
reference cells (i.e. the cell in A31 was a reference to A7).
 
J

Jon Peltier

I named the worksheets Sheet1 and Sheet2. You have to use your own sheet
names.

- Jon
 
A

artemis

Re #1 query. You can also open the Pivot Table Wizard and use the back
button to select a different /larger range.
 
G

Graeme at Raptup

Yes,
I saw that.
This is the code I used
Sub ColorByCategoryWorksheet()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range
Set rPatterns = Worksheets("LOBTotal").Range("A31:A38")
With Worksheets("LOB Chart top 8").ChartObjects(1).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rcategory.Interior.ColorIndex
Next
End With
End Sub
 
J

Jon Peltier

This is exactly the code I used, and it worked no matter which sheet was
active:

Sub ColorByCategoryLabel2()
Dim rPatterns As Range
Dim iCategory As Long
Dim vCategories As Variant
Dim rcategory As Range

Set rPatterns = Worksheets("Category Label").Range("A1:A4")
With Worksheets("Test").ChartObjects(1).Chart.SeriesCollection(1)
vCategories = .XValues
For iCategory = 1 To UBound(vCategories)
Set rcategory = rPatterns.Find(What:=vCategories(iCategory))
.Points(iCategory).Interior.ColorIndex = rcategory.Interior.ColorIndex
Next
End With
End Sub

It looks like the code I posted and like the code you posted. Which line is
highlighted when the program crashes? Which array do you think has the out
of range subscript (worksheets? vCategories? Points? ChartObjects?
SeriesCollection?)?

Is your chart a standalone chart object, or is it embedded in the worksheet
named "LOB Chart top 8"? If it's a standalone chart sheet, use this:

With Charts("LOB Chart top 8").SeriesCollection(1)

- Jon
 
G

Graeme at Raptup

Hi Jon,
the highlighted line is
With Worksheets("LOB Chart top 8").ChartObjects(1).Chart.SeriesCollection(1)

it is a standalone chart in which case I changed it to

With Charts("LOB Chart top 8").ChartObjects(1).Chart.SeriesCollection(1)

But now get a run time error (1004) "Unable to get the chart objects
property of the chart class"
 
J

Jon Peltier

Try this:

With Charts("LOB Chart top 8").SeriesCollection(1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top