Pivot charts & tables
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! |
Pivot charts & tables
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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
Thanks Jon,
that's a great help. Have not tested yet but am sure I can follow what you've suggested "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
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.SeriesC ollection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
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). "Jon Peltier" wrote: 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.SeriesC ollection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
I named the worksheets Sheet1 and Sheet2. You have to use your own sheet
names. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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). "Jon Peltier" wrote: 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.SeriesC ollection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
Re #1 query. You can also open the Pivot Table Wizard and use the back
button to select a different /larger range. |
Pivot charts & tables
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 "Jon Peltier" wrote: I named the worksheets Sheet1 and Sheet2. You have to use your own sheet names. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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). "Jon Peltier" wrote: 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.SeriesC ollection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
Thanks,
but I have over 10 tables & charts so that is not really convenient "artemis" wrote: Re #1 query. You can also open the Pivot Table Wizard and use the back button to select a different /larger range. |
Pivot charts & tables
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.SeriesCol lection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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 "Jon Peltier" wrote: I named the worksheets Sheet1 and Sheet2. You have to use your own sheet names. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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). "Jon Peltier" wrote: 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.SeriesC ollection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
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" "Jon Peltier" wrote: 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.SeriesCol lection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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 "Jon Peltier" wrote: I named the worksheets Sheet1 and Sheet2. You have to use your own sheet names. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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). "Jon Peltier" wrote: 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.SeriesC ollection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
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 _______ "Graeme at Raptup" wrote in message ... 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" "Jon Peltier" wrote: 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.SeriesCol lection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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 "Jon Peltier" wrote: I named the worksheets Sheet1 and Sheet2. You have to use your own sheet names. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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). "Jon Peltier" wrote: 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.SeriesC ollection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
Pivot charts & tables
Thanks Jon,
works fine! Thanks for sticking with me on this! "Jon Peltier" wrote: 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 _______ "Graeme at Raptup" wrote in message ... 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" "Jon Peltier" wrote: 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.SeriesCol lection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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 "Jon Peltier" wrote: I named the worksheets Sheet1 and Sheet2. You have to use your own sheet names. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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). "Jon Peltier" wrote: 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.SeriesC ollection(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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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!? "Jon Peltier" wrote: 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(PivotDataSh eet!$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/200...ategory-label/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Graeme at Raptup" wrote in message ... 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! |
All times are GMT +1. The time now is 12:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com