Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart from pivot data, and sheet data?
Hi.
The latest request is to do a chart which is partially from data in a pivot table, and partially from static data in another sheet. The pivot table itself is based upon too many rows to handle in Excel 2003... there's a pivot cache built from an ADO recordset, returned from Oracle tables. Is it possible to present a chart with the data mentioned which would be dynamically updated when the selections in the pivot table are changed? If not, here's what I've thought up as of now: 1) Fire code when the sheets' PivotTableUpdate event is fired 2) Have that code Copy PasteSpeckial values the data in the pivot table 3) Create the chart off of the static version of what was selected in the pivot table, and the static data from the other source sheet. That should work... but, I'm open to suggestions. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart from pivot data, and sheet data?
You can use pivot table data in a regular chart, but you have to specify it
carefully. Create a chart based on the non-pivot data, or starting with a blank cell (no data). In step 2 of the wizard, or after creating the chart, from Chart menu Source Data, click on the Series tab, add your series on at a time, and select the X and Y value ranges. If you start with any data in the pivot table selected, the chart becomes a pivot chart. If you use the Data Range tab, the chart becomes a pivot chart. The pivot table can still be live, you don't have to paste values. However, the chart will not automatically update its data range with the change in the pivot table, unless the pivot table only changes one dimension, which is the number of points in a series, and you have a dynamic name defined to track that. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mark" wrote in message ... Hi. The latest request is to do a chart which is partially from data in a pivot table, and partially from static data in another sheet. The pivot table itself is based upon too many rows to handle in Excel 2003... there's a pivot cache built from an ADO recordset, returned from Oracle tables. Is it possible to present a chart with the data mentioned which would be dynamically updated when the selections in the pivot table are changed? If not, here's what I've thought up as of now: 1) Fire code when the sheets' PivotTableUpdate event is fired 2) Have that code Copy PasteSpeckial values the data in the pivot table 3) Create the chart off of the static version of what was selected in the pivot table, and the static data from the other source sheet. That should work... but, I'm open to suggestions. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart from pivot data, and sheet data?
okay, thanks, Jon.
I may give that a try. Another thing I thought of since the post would be to use =GETPIVOTDATA() formulas to summarize the pivot's current selected data... And then do the chart off of the range with the =GETPIVOTDATA() formulas, and the static data. Thanks for you suggestion! Mark "Jon Peltier" wrote: You can use pivot table data in a regular chart, but you have to specify it carefully. Create a chart based on the non-pivot data, or starting with a blank cell (no data). In step 2 of the wizard, or after creating the chart, from Chart menu Source Data, click on the Series tab, add your series on at a time, and select the X and Y value ranges. If you start with any data in the pivot table selected, the chart becomes a pivot chart. If you use the Data Range tab, the chart becomes a pivot chart. The pivot table can still be live, you don't have to paste values. However, the chart will not automatically update its data range with the change in the pivot table, unless the pivot table only changes one dimension, which is the number of points in a series, and you have a dynamic name defined to track that. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mark" wrote in message ... Hi. The latest request is to do a chart which is partially from data in a pivot table, and partially from static data in another sheet. The pivot table itself is based upon too many rows to handle in Excel 2003... there's a pivot cache built from an ADO recordset, returned from Oracle tables. Is it possible to present a chart with the data mentioned which would be dynamically updated when the selections in the pivot table are changed? If not, here's what I've thought up as of now: 1) Fire code when the sheets' PivotTableUpdate event is fired 2) Have that code Copy PasteSpeckial values the data in the pivot table 3) Create the chart off of the static version of what was selected in the pivot table, and the static data from the other source sheet. That should work... but, I'm open to suggestions. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart from pivot data, and sheet data?
You could, but it's an extra layer.
- Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mark" wrote in message ... okay, thanks, Jon. I may give that a try. Another thing I thought of since the post would be to use =GETPIVOTDATA() formulas to summarize the pivot's current selected data... And then do the chart off of the range with the =GETPIVOTDATA() formulas, and the static data. Thanks for you suggestion! Mark "Jon Peltier" wrote: You can use pivot table data in a regular chart, but you have to specify it carefully. Create a chart based on the non-pivot data, or starting with a blank cell (no data). In step 2 of the wizard, or after creating the chart, from Chart menu Source Data, click on the Series tab, add your series on at a time, and select the X and Y value ranges. If you start with any data in the pivot table selected, the chart becomes a pivot chart. If you use the Data Range tab, the chart becomes a pivot chart. The pivot table can still be live, you don't have to paste values. However, the chart will not automatically update its data range with the change in the pivot table, unless the pivot table only changes one dimension, which is the number of points in a series, and you have a dynamic name defined to track that. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "mark" wrote in message ... Hi. The latest request is to do a chart which is partially from data in a pivot table, and partially from static data in another sheet. The pivot table itself is based upon too many rows to handle in Excel 2003... there's a pivot cache built from an ADO recordset, returned from Oracle tables. Is it possible to present a chart with the data mentioned which would be dynamically updated when the selections in the pivot table are changed? If not, here's what I've thought up as of now: 1) Fire code when the sheets' PivotTableUpdate event is fired 2) Have that code Copy PasteSpeckial values the data in the pivot table 3) Create the chart off of the static version of what was selected in the pivot table, and the static data from the other source sheet. That should work... but, I'm open to suggestions. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart from pivot data, and sheet data?
Another thing I thought of since the post would be to use =GETPIVOTDATA()
formulas to summarize the pivot's current selected data... You could, but it's an extra layer. Yeah, I see that now. I had thought of it before I saw your info that you can just add Pivot data directly to a chart. I went ahead and did the first part of your suggestion last night... put the pivoted data into the chart, piece by piece. It's not my data, and I'm a little unclear as to the source of the capacity data to be charted against it... have a question into the data owner on that, now. I think it will just be one dimension of the Pivot that is changing... there's one column field in it, and they would be selecting different ones of them. The row fields are year and month in one, and week in another... those might be different. But I think I'm ready to tackle creating the dynamic formula that tracks how many columns are currently visible in the pivot table, to be used in the definition of the SERIES in the chart. Thanks for your help, Jon. I've looked through your website before, and know the type of thing you can do. I've put your idea on clustered stacked bar charts (by creatively charting blank space, essentially), to use a couple of times. thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart from pivot data, and sheet data?
Jon,
I've looked at your downloaed the zip of the dynamic chart... I see what you're doing there. I think it's complicated a little bit by the fact that one source is from the pivot, and one source from the static capacity grid (I don't think I said that before... the pivot data is extracted shop floor hours requirements, and the static data is a capacity definition). But, first, I'm not sure they need the chart to be dynamic... but it would be nice. I think it's doable, in a couple of ways. One would be to lump the capacity data into the pivot, with a defining field... might run inot some issue with that. And another way woud be to do the series definition of the capacity similarly to the dynamic series definition of the pivot data. Probably run into a bump here and there getting that in, but it should work. Thanks again for your suggestions. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Chart Data Data range cannot be changed | Excel Discussion (Misc queries) | |||
Duplicating Pivot Chart / Changing Pivot Chart data range | Excel Discussion (Misc queries) | |||
chart from pivot data does not update x-axis bar chart values - bug | Excel Discussion (Misc queries) | |||
Can I display the actual data in the data field of a pivot chart? | Excel Discussion (Misc queries) | |||
Pivot Chart Data from external data source | Excel Programming |