Chart Based On Pivot Table Event Processing
May 8, 2007
I have an Access application with a form containing an Excel chart based on a pivot table based on an Access query based on an Access table which is refreshed every 5 minutes using a Timer event. When the table is refreshed the Excel chart is refreshed. The chart monitors some processes and is active throughout the day. As the day progresses more and more data points are available for plotting on the x-axis. The x-axis labels get very crowded as the day progresses and I need to programmatically change the x-axis scale at various times of the day. To that end I am trying to find an event that is activated at some point during the automatic refresh of the chart/pivot table.
I have tried the Workbook_Change event, the Worksheet_Change event, and the Chart_SeriesChange event. None of these are executed during the refresh process or I have done something wrong in trying to use them. At the moment they only have a MSGBOX message in them so that I can determine if they are even being executed.
View 9 Replies
ADVERTISEMENT
Dec 19, 2008
My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way.
So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well.
This solution also worked perfectly, but now I've been told to create the graph without macros.
Does anyone have any suggestions? The requirements/details are as follows:
1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH"
2. The Pivot Table has two criteria - School Name and Year Level
3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels
Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
View 9 Replies
View Related
Nov 19, 2006
When you double click on a number in a pivot table data area you get a new sheet with that information showing the detail, is there anyway to "trap" this action?
I want to run a macro on the detail data but can't see a way to do it automatically, have tried, BeforeDoubleClick, PivotTableUpdate, NewSheet events but all have there draw backs if you are doing something other than showing the detail for the pivot table. Of course I can just run the macro after the sheet is made but it would be nice to do it on its own.
View 9 Replies
View Related
Oct 2, 2007
I am trying to write a worksheet_selectionchange macro in which the change is initiated only when certain cells are changed (B6:B9 and X9). I do not want the change to be initiated when any other cells on the worksheet change. B6:B9 and X9 are drop down boxes and the values within the drop-down boxes is variable.
View 3 Replies
View Related
Mar 22, 2012
I have three worksheets Sheet 1, Sheet 2 and Sheet 3 with three Pivot Tables
Sheet1 - Sheet1Pvt1
Sheet2 - Sheet2Pvt2
Sheet3 - Sheet3Pvt3
When I use In Sheet 2 the following lines of code
Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Application.EnableEvents = False
Application.ScreenUpdating = False
MsgBox "The pvt table refreshed " & Target.Name
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
and even If I Refresh Sheet 3 pivot table, Sheet 2 Event is triggered and it prints "The pvt table refreshed Sheet2Pvt2";
How to restrict the event code only to Sheet 2 and pvttbl Sheet2Pvt2.
View 3 Replies
View Related
Jan 3, 2013
If I want to produce a pivot chart using VBA can I do it without producing the pivot table?
I am guessing that the following are a possibility:
Do in a virtual sheet that is not shownhide the sheet (can do already)
I am looking for the virtual sheet option or something similar.
View 2 Replies
View Related
Jun 21, 2006
Is it possible to use the chart wizard for a Pivot Table yet maintain that chart in the same worksheet and not have it in another? Every time I click on the wizard a chart is created on a separate page.
View 2 Replies
View Related
Sep 17, 2009
I have attached a portion of my Pivot Chart in Excel 2003 and have been trying to recreate this in 2007 with some difficulty. I am particularly interested in displaying the field list headings (specifically 'Count of Hit or Miss') in the pivot chart in 2007 but cannot make it work.
View 4 Replies
View Related
Mar 29, 2007
I have a data table and want to create six charts. In theory pivotcharts would be the way to go. However every time I refresh the charts they revert to excel's default chart and all my colour scheme and nice layouts are lost.
View 3 Replies
View Related
Apr 28, 2014
I have a list of customer satisfaction scores that are pulled in from a sharepoint list. The list is then used to create a pivot chart that is used in a web part on a dashboard in sharepoint. The chart is supposed to show average customer satisfaction scores per category per month.
The problem I have is that I can't filter the categories using a slicer, I can only filter the categories' values (i.e. the scores). I can filter by date though.
When I try to filter the various score categories/series (Support, Change Management etc) I only get the option to filter their values i.e. 1,2,3,4,5.
I want to be able to use a slicer or similar to be able to display either all of the series or just selected ones.pivottable.JPGpivotchart.JPG
View 14 Replies
View Related
Apr 30, 2014
Was working on this problem for a poster: [URL] ......
I can't seem to get the pivot table/chart to format exactly as I desire. It seems that as soon as I add 'group by hour and day' Excel forces the formatting to AM/PM and I want to keep it military. I want to group by hour, so that data that occurs at 6:00 and at 6:30 are grouped, and I had to group by day so that 6:00 on 1/1 was separated from 6:00 on 1/2.
Attached is a worksheet which shows the desired chart format (not a pivot chart), and the attempted pivot chart. I want the pivot chart to match the 'simple' chart in look and feel. Any attempts to change the formatting of the row labels to 'h' is promptly ignored by Excel.
Note the two tasks that occur at hour 18 (one at 18:00 and the other at 18:20 (you will need to see the formatting to truly see the minutes)). Those should be combined in the pivot table (and they are) and on my 'adjusted' table (where I used SUMIFS).
View 2 Replies
View Related
Aug 14, 2014
Or at least with a formula?
This is the chart formula for the report:
=SERIE(Report!$B$10;Report!$A$11:$A$30;Report!$B$11:$B$30;1)
View 3 Replies
View Related
Mar 3, 2009
I have created some pivotcharts in Excel 2007, but I have a serious problem with them The labels (usernames actually) in the data table are impossible to read (pls see attachment) If I make the letters smaller it is unreadable, if I make them larger it is still the same. how I can make the data table "resized"?
View 4 Replies
View Related
Feb 4, 2010
I have recently noticed a problem with a pivot table and assoicated pivot chart. The table has numerous columns from date, location and one entitled route. The route column has a value which is either a number or 'other'. The formatting of the column has been set to general (although the same problem was present under text). The problem is as follows:
When a new row of data is entered where the route is the same value as previous rows it should be grouped together. However when I go into my pivot table and look at the options for route I find the same value repeated twice. ie a route value of 155 will be listed once at the top and then lower down the list. A temporary fix is to rename all values of 155 to x155 and then back to 155. This then has everything grouped again under one value in the pivot table. As soon as new entries are added though the problem repeats itself. Does anyone know why this might be?
View 4 Replies
View Related
Dec 4, 2012
My pivot table shows Towns as the Row headings and Month as the column headings. When I do a Chart, I get Towns on the X axis and Months as Y. I want the chart to show all my May data in a stacked column, then all my June data in another stacked column, etc. If I pick "Select Source Data" the options are greyed out. What's a mother to do?
View 1 Replies
View Related
Mar 17, 2008
I can not get my Pivot Chart to count, sort and categorized my data. I have included a sample file for your viewing. I basically want the data in cells C2:E18 counted, sorted and totaled by categories and locale. I thought the pivot charting was simple, but I'm having quite a challenge with this one. The pivot chart data should reflect as my sample indicates in cells E24:K27
View 3 Replies
View Related
Aug 16, 2013
I have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
View 3 Replies
View Related
Jul 30, 2012
Can Pivot Table chart add another data from another sheet? I attached the link for this file (Add data1), it is because the file consists of several sheets and I do not know how to show here.
I would like to add the data from the "Rate" sheet into the Pivot Table chart (Chart.PT). I made an example by using normal way (Chart.Case (9)), the column series in the chart is the one I added from the "Rate" sheet. I wonder if I could do the same in pivot table chart.
View 9 Replies
View Related
Sep 5, 2006
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
View 3 Replies
View Related
Aug 13, 2014
I have table with companies across in columns, months down the side in rows, and 6 parts to the data in addition to company and month. I have quotes from 3 different sources, and won deals from those 3 different sources. How do I create a pivot table/chart to analyze the data quickly, or find out Top 10 companies quoting with value and top 10 companies with won deals?
I have attached an example, taking out the company names with A, B, C, and source with A, B, C. The cells do have an array formula which picks data from another spreadsheet. Which, by the way, is really slowing my machine down each time I type something into the spreadsheet.
Quotes.xlsx
View 14 Replies
View Related
Oct 31, 2008
I have linked a graph to update from a pivot table. I would like to print a copy of a graph and pivot table for each item in the page field.
Can a macro loop through each page field and print?Is there a way to send them to the printer all at once? (I noticed the printer hangs when manually printing page by page)
I've tried "view all pages of pivottable" but then I lose the view of the chart. I tried recording the events but the number of items in the page field changes with each data update and I'm not sure how to write code to accept this.
View 8 Replies
View Related
Feb 26, 2014
I am trying to display data on a chart and dynamically change the items to display by manipulating the filters. What I cannot figured out is how to display the Pivot Table Grand Total column on the chart. This is the total that the pivot creates and there is no total field in the data. Hope this explanation I gave is clear.
View 1 Replies
View Related
Jun 4, 2012
I have a user that uses pivot tables and charts every month to do a report. He wants to copy his charts every month and then just change the month in the data for the pivot table, but not matter what he tries its tied to the previous month and then chart data range in the select data source properties is grayed out. how to get this to work?
View 3 Replies
View Related
Sep 24, 2011
I am stuck trying to sort data that looks like the following, into a format that I can use as source data for pivot tables/charts.
Excel2007ABCDEFGHIJK2MalayMalayMalayMalayMalayMalay
ChineseChinese3MaleFemaleFemaleMaleFemaleFemaleMaleMale
436-4536-4536-4525-3525-3525-3536-4536-455
DateArticleContentLapseCurrentCurrentCurrentLapseLapseCurrentCurrent612-SepRelieving 40 years - Oct 15, 1972
Thick Haze continues to blanket MalaysiaHistory42712-SepRelieving 40 years - May 5, 1972
All Sharifah wants is a pair of legsHistory1222812-SepA man and his agonyHistory3223912-SepA lesson on Sept 16History22Sheet2
I will need to group Data multiple ways. However, I cant work out a good way to sort it. Can I have a suggestion on layout that I will be able to arrange data appropriately.
View 2 Replies
View Related
Mar 21, 2007
1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.
2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?
3) am i going about this the wrong way with the objects? should i be after vba code?
View 4 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Feb 7, 2014
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1
2
3
4
Here's what each of those "mean" (I have this in another table):
1 - Sick
2 - Overtime
3 - Court
4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
View 8 Replies
View Related
Jan 9, 2014
From what I understand there's no way to do this without using macros. I would perfer not to use macros, but I need my PivotTables to auto-refresh anyways and apparently that will require a macro, so oh well. I'm very new to macro coding so I can't seem to successful apply any of the previous threads about this to my sheet.
I just need my pivot table on sheet "Customer" to filter the customer field based on the value in cell C1. Also I need to make sure the sheet doesn't "freak out" if the value in C1 is either blank, or is a customer value which doesn't exist in the table. Preferably in this scenario it would display nothing on the pivot table, but I don't know if that's possible. I want the sheet/macro to allow an invalid filter value in C1 just so the sheet doesn't lock up until it is corrected.
I also need my workbook to auto-refresh all the pivot tables is that's easy to code in as well.
View 2 Replies
View Related
Jan 27, 2014
filter my pivot table using a cell value, rather than manually selecting from the dropdown.
The report filter title is Rnd H, and values start from 0 through to 2, at .1 intervals (so 0.1, 0.2, 0.3, 0.4 etc).
Rather than select "0.3" manually from the dropdown, can the pivot table do this if "0.3" was written in cell A1 for instance?
View 5 Replies
View Related
Mar 11, 2014
I try filter a pivot table based a cell.
[Code] .....
Attached File : FilterPT.xlsx
View 3 Replies
View Related
