I am trying to filter pivot chart by strategy and it does not update the combined Sum of Cumulative chart when I uncheck / filter strategy.
I have attached the example : Strategy_Combined_Analysis_Wkly_All_Example.xls
I have this problem which I have simplified below:
Sheet 2 contains a Pivot table. One of the report filters is "Month-Year" (Eg, May-14 - in date format 'mmm-yy') which is selected by a Slicer on sheet 1.
Cell D1 on Sheet 2 contains a formula which calculated/displays the mmm-yy of the same period last year (eg May-13).
I want a code so that when I select the 'Month-Year' Slicer to filter the pivot table on Sheet 1 (eg to May-14), another Pivot on Sheet 3 is filtered with the previous year mmm-yy (May-13).
I now have code that automatically refreshes all pivots and send an email with a brief body and summary table. What I wanted was to have VBA update the pivot table to the latest date before sending out the summary.
The table below is the mentioned pivot above where normally, I would update the Date to the latest date where a value was updated. So where PivotTable11, take Max of Date (only one row)
Date Amount #Volume
04/07/2013 £5,500.001
I have a macro to refresh all pivottables in my workbook. Each pivottable source from the same data pool - in addition I have a filter on each pivottable. Is there a way to fix this filter, such that once the source data is updated, the filter doesn't change? Or to only refresh the "data" in the pivottable?
My code for refreshing all pivottables is given below.
Dim pvt As PivotTable
Dim sh As Worksheet
Application.Calculation = xlManual
For Each sh In Worksheets
For Each pvt In sh.PivotTables
pvt.RefreshTable
Next pvt
Next sh
Calculate
Application.Calculation = xlAutomatic
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
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?
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?
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
I am using Microsoft Excel 2003. There are several charts in the worksheet and the ranges for the series in the charts are defined by named ranges, e.g.:
Values: =FirstNamedRange
where FirstNamedRange is defined as Sheet1!A2:A100.
The values in FirstNamedRange are themselves calculated from other inputs. The problem is that when I recalculate the sheet, changing the values of FirstNamedRange, there are times when the chart does not change.
This happens sporadically. 9 times out of 10 both the values in the range and the chart change. At other times, the values in the range change and the chart doesn't reflect this. If I save the file, close and then reopen, (thus forcing the chart to recalculate?) then the chart looks correct.
I have a chart that display data from a dynamic named range. Problem is the chart only displays the correct data when I first input the named range into the data source on the first attempt. When the dynamics of the named range change, the range the chart displays remains the same. I have read another post on here where someone had the solution, but I couldnt get it to work for me.
I am inputting the named range into Data Source-> Data Range. The other thread said to put the named range into Data Source-> Series. I am unable to enter my named range into Series. I have also tried putting the .xls filename followed by !(named range) into the series boxes but excel will not allow me to do this either.
I have a chart with 3 series':
Series1
Series2
Series3
The chart is called chtEngagement. I want to use vb to update these series'.
Series1 should be shown as:
I have a number of charts (mostly pie) I don't have a legend, but I do have "Show label and percent" selected.
When I add in new data the actual chart updates to show the right sized pie slices... but the values on the labels don't update as well. I can't figure out how to make that happen?
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies View RelatedI have created a dashboard based on a number of pivot tables. (Using GETPIVOTDATA tags)
One of the fields in the Dash tab is Week which is a drop down menu of 1-13. (GLA Dash C3)
I have two pivot tables in seperate tabs. (BottomQuartile1 and BottomQuartile2) both have Week as Report Filter.
At the moment when I change the week in Dash from 4 to 5 i have to manually go into the two BottomQuartile tabs and alter the week to match.
Is there anyway I can automate this process so that when i update the week in Dash it will update the Week in the other two tabs?
I have created a spreadsheet which uses data validation to allow a user to select a company, start Date, end date, and projected months in order to output different figures. I have attached a spreadsheet to this post in case that was hard to understand.
Since there is data validation, indexing, and matching going on, as different selections are made, various tables change as a result. Uou will be able to see what I mean as you open the example sheet and start messing around with the data validation boxes.
The problem I am encountering now, is creating an exponential graph that will also keep changing as the user changes their selections in the data validation boxes. I am unsure if this is even possible, but I figured it was worth a shot to ask. On the example sheet, I have included a graph similar to the one I want, but it only contains data in a certain range of cells, rather than dynamically changing to accommodate whatever data is outputted.
Here is what I am looking for in the graph:
1. Whatever dates are selected by the user in the data validation boxes, along with the projected dates will be on the x-axis.
2. The PMPM figures associated with the selected dates, and the projected dates on the y-axis.
3. Ability to make different selections from the data validation boxes and still have a dynamic chart that keeps updating itself.
VBA code that would update a chart automatically as you change the inputs which are located in cells in excel. The inputs are located in cells B5:B9 and i want a code so that as you change these numbers the chart would automatically update.
View 1 Replies View RelatedIs it possible to update Chart Titles in Batches. For the following Titles:
'Client 1, Jan 07'
'Client 2, Jan 07'
'Client 3, Jan 07'
'Client 4, Jan 07',
Would you be able to change 'Jan 07' to Jan 08' on all of the above whilst keeping the variable client name, in a single batch process?
I have the same question as described in Graphs Not Updating When Source Data Updated; however, the poster did not reply to the request-for-clarification questions.
My chart does not always update when my source data changes. My Calculations setting is Automatic (not Manual). There is no macro code working to produce the source data or the chart. My chart is on the same worksheet as my source data. The source data is simple links to other cells in the same workbook that update as the underlying data is updated. Saving doesn't work. Manually calculating (F9) doesn't work. The only way I have found to get the correct data to display in the charts is to open the Source Data and re-enter the Series range. Obviously, that gets old.
I am dealing with plotting large amounts of data (millions of cells) across multiple series. Everytime you click, add a new series or do anything it redraws the graph taking a while. This Is there are way to prevent Excel 2003 from plotting a graph until you are done setting it up?
View 3 Replies View RelatedI have created quite a nice little macro that;
drills through a lot of key figures, updates a pivot chart and copies the chart to powerpoint.
However it crashes on one particular data set every time with this error:
Run-time error '1004':
Unable to set the _Default property of the pivot item class
The code where the debugger stops is the last line below here.
lngKpi = Sheets("Helpfile"). Cells(lngRow, 2)
Sheets("Charts").Select
ActiveSheet.ChartObjects("DK").Activate
ActiveChart.PivotLayout.PivotTable.PivotFields("KPI # (overall").CurrentPage = lngKpi
I can manually change the pivot chart to the keyfigure it crashes on and thereby workaround the problem in the macro. Also it works for more than 50 other keyfigures without problems.
This line of code works fine on a standard pivot table running from an excel data source, it updates Product on the change of combobox1. Easy!
Sheet4.PivotTables("PivotTable4").PivotFields("Product").CurrentPage = _
ComboBox1.Text
However! I have a Pivot table running off an OLAP data cube and the same line doesn't work!
Its errors with 1004 unable to get the pivotfields property of the pivottable class?
I've created a spreadsheet which, on running a macro, imports data from a text file, formats it and then sums various parts of it giving me a subset of the large file as a range of data for a chart (a pie chart) on Sheet 2.
The problem begins when I delete the data in the worksheet in preparation for another import - the Pie chart just loses all the data and I have to manually reselect it in the chart each time.
Once deleted the chart is blank, but as soon as the data is imported, I get reference errors when I click on Sheet 2
I must be doing something wrong. You can delete data and repopulate it and the chart should pick up with the new data. If the cells it is referencing are empty, it is blank. Not so here.
Is it to do with importing the data? Or the formula for my chart data?
I have a worksheet with 300 rows and I have a separate worksheet that contains graphs based on the data in the 300 rows.
The data is continually updated but stays at 300 rows. I add a new row at 301 and then I delete row 1.
I set the graphs up with a Chart Data Range of =GData!$B$1:$AB$300
As I add and delete rows the Chart Data Range reduces so after adding and deleting 3 rows the Chart Data Range is now =GData!$B$1:$AB$297
How can I get the Chart Data Range to stay at =GData!$B$1:$AB$300?
I have 9 pivot tables on different tabs that I would like to update based on a date I reference (date input in A1 ex. 9/1/2013). Is it possible to update the filter on all of the pivot tables by referencing a cell that would be the filter (date), so the pivot table will only return values for the date entered in A1?
View 6 Replies View RelatedI've got a macro that adds new data to a sheet and updates an existing pivot table.
It's been working fine but ever since I've had to have two items in a field hidden it won't show any new data in the pivot table.
For example, the field firm name has two items hidden after the update any new firm names that weren't in the report before won't show up in the pivot table. The pivot table recognizes that it's part of the full data set but the checkboxes are all unchecked for the new firm names.
this is the code i"m using to update the pivot table:
'Update Pivottable
DSRWKB.Activate
DSRWKB.Sheets("Pivot").Activate
[Code].....
Please look at the sample workbook below. The chart you see below is fine BUT I would like in a pivot table chart, so I could select any days on the pivot chart. I have tried it to do it myself but for some reason the time is not displaying on the pivot chart correctly. Any help please ?
P.S. If not possible then can we add a scroll bar or something?
I have created a pivot table and chart. The pivot chart consists of three sets of data from the pivot table. Two of the data sets are lines and the third data set is displayed as columns. I made the third data set into columns by right clicking on the data series line and selecting chart type. I change the chart type for that data set to a column and it worked great. The only problem is that when data is refreshed in the associated pivot table, the entire chart turns into columns with completely different formatting. Does anyone know how I can maintain the graph with the two lines and one column data set when I refresh the data?
View 4 Replies View Related I might use the filter as a selection from a combobox, or I might honestly wish to create 15 separate charts.
I have a spreadsheet. Three columns out of maybe six in immediate interest: Date, Name and score.
In "name" I have about 15 different students. I wish to create a chart that will extract the data for one of them which I would select - on the vertical ("y") axis have the scores (from "score") which range in whole numbers from 1 to 9, and on the horizontal ("x") axis show the dates (from "Date") of each score.
Complicating my thinking is the fact that a date can have more than one score, but seldom more than three.
I would then want to add a trend line through the graph, which, if I recall, is fairly easy to do.
I am wondering if I might at some point need to control the length of history, for though now I am working with about three months and over that period of time no one has more than 18 total scores.
I have a MS Office Chart Component (11) on a userform. I am trying to programatically change the item in the filter (page) area (only one field) using VBA. The source is an OLAP cube ( SQL AS 05) but don't think that's particularly relavent because I can't do it even if the source is a range.
The object comes as a ChartSpace on the userform. I have found a reference (using the watch window) to the current filter selection in ChartSpacex.InternatPivotTable.ActiveView.Fieldsets(0).Fields(0)....
I want to filter a pivot using VBA by 'Contains' like it is possible to filter data in a spreadaheet. Is it possible?
Im currently using the coding :
ActiveSheet.PivotTables("PivotTable2").PivotFields("Producing Office"). _
CurrentPage = "Choice"
I want to replace my choice with a contains "choices".