YouTubersLoveExcel#25: Dynamic Range for Pivot Table & Chart

Hello! Just wondering if anyone can help me.

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?

Thanks!

Frustrated Tim!

I had a sheet with columns:
S.No Tran ID Value Date Description Cr/Dr Dr.Amt Cr.Amt & Bal.
I want to build Pivot Chart based on a pivot table with this data. the data for this columns keep Adding every day.how would i design a dynamic chart with dynamic pivot table in the same sheet.
I m attaching the data file.

how do you create pivot table for each of the company's share and another pivot table that combines all the companies' share? the data should be converted to percentage in the tables.

how to make the pivot table update whenever there is new data? and the chart should update too, be it auto or manual.

how to make the pivot table check for null first, before showing the percentages? cause the new data might have 'NaN', which means there is no closing price for that particular day. how to make the pivot table so that it will ignore the 'NaN', but not delete it?

some sample data (they are stored in different worksheets):
these are the closing prices of company 1(Asia, sector 1)'s share.
Date Close Wednesday, January 02, 2008 56.32 Thursday, January 03, 2008 57.65 Friday, January 04, 2008 57.2 Monday, January 07, 2008 58.4 Tuesday, January 08, 2008 58.9 Wednesday, January 09, 2008 58.74 Thursday, January 10, 2008 57.75 Friday, January 11, 2008 57.03 Monday, January 14, 2008 57.33 Tuesday, January 15, 2008 55.74 Wednesday, January 16, 2008 54.49 Thursday, January 17, 2008 53.46 Friday, January 18, 2008 53.25 these are sample data for company 2(Asia, sector 2)'s closing share prices.
Wednesday, January 02, 2008 14.24 Thursday, January 03, 2008 14.36 Friday, January 04, 2008 14.06 Monday, January 07, 2008 13.92 Tuesday, January 08, 2008 13.94 Wednesday, January 09, 2008 13.79 Thursday, January 10, 2008 13.39 Friday, January 11, 2008 13.43 Monday, January 14, 2008 13.43 Tuesday, January 15, 2008 13.01 Wednesday, January 16, 2008 12.71 Thursday, January 17, 2008 12.55 Friday, January 18, 2008 12.3 these are sample data for company 3(Non-Asia, sector 1)'s closing share prices.
Tuesday, January 01, 2008 247.9 Wednesday, January 02, 2008 243.3 Thursday, January 03, 2008 254.85 Friday, January 04, 2008 261.25 Monday, January 07, 2008 260.85 Tuesday, January 08, 2008 251.45 Wednesday, January 09, 2008 252.8 Thursday, January 10, 2008 241.8 Friday, January 11, 2008 239.2 Monday, January 14, 2008 241.05 Tuesday, January 15, 2008 240.2 Wednesday, January 16, 2008 231.65 Thursday, January 17, 2008 234.7 Friday, January 18, 2008 222.8 these are sample data for company 4(Non-Asia, sector 2)'s closing share prices. Tuesday, January 01, 2008 4950.5 Wednesday, January 02, 2008 5073.8 Thursday, January 03, 2008 5093.75 Friday, January 04, 2008 5379.4 Monday, January 07, 2008 5295.95 Tuesday, January 08, 2008 5151.45 Wednesday, January 09, 2008 5058.5 Thursday, January 10, 2008 4927.35 Friday, January 11, 2008 5055.7 Monday, January 14, 2008 5017.65 Tuesday, January 15, 2008 4902.65 Wednesday, January 16, 2008 4792.95 Thursday, January 17, 2008 4656.45 Friday, January 18, 2008 4382.3 end-result of the pivot table (all 4 companies combined):
this pivot table should allow data analysis/filter based on
i. by year: All, 2009, 2008,....
ii. by market sector: All, Asia, Non-Asia,....
iii. by industry sector: All, sector 1, sector 2, ...
iv. by month: All, Jan,...., Dec


then from this pivot table, how to create pivot chart?
end-result of the pivot chart:



please help!
thank you very very much!

I use pivot table and X-Y Scatter plot for analysis of data. Is it possible for scatter plot to select the chart series automatically, as I select the different pivot table fields?

I use x values as the 'Row Field', and Y values with 'Data Items'. And 'Column Field' as a different series. (Please check first figure [pivot.jpg])

Is it possible to write a macro which automatically plots
column field - as Series Name
Row field - as X values
data items - as Y values

(I have figured out how to plot X-Y Scatter plot for Pivot table, which is generally not allowed.)

Consequently, Is it possible to do that same if there are more than one column fields? (Check Figure 2 [pivot2.jpg])

I have worked with excel macros in the past. But for this particular problem, I am not able to come up with any logic so far. So, please help.

I hope that I have explained the problem clearly. Thanks.

Hello
I have a spreadsheet with dates in colum A and numbers in columns B to D. The dates span a full year and I have charted those dates and their values on a line chart. I want to be able to provide two drop downs or a free text field where the user can select two months they want to display in the chart and this will update the chart data accordingly.

Is this possible without using pivot tables?

Thanks

Hi One and All

I have a pivot table with 9 differing column criteria and I want to graph the total column, using the references from the first column of the pivot table.

To do this, I have created a formula that looks at the first column for the reference (if(a1="Grand Total","",A1) and looks at the total column (using "Getpivotdata").

I have then created a pivot chart from the two columns (reference and total), ignoring the Grand Total row.

Now if I remove a column from the pivot table, the number of lines may reduce and Grand Total moves up a row or two.

I can use indirect and match to get either the Grand Total cell or the G/t value in another cell in my spreadsheet, but I cannot seem to be able to get the data for the pivot chart to refresh to not include the Grand Total, which then skews the graph.

I hope that I have given enough explanation such that someone might be able to help me.

Thanks in advance.

Regards


simon

Hi

I have created a macro that creates a pivot table based on raw data. Pretty basic.
However the number of rows in the raw data will change daily, therefore I need to change the code in my macro, how can I do this?

Code is:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R1186C16", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="PivotTable4", DefaultVersion _
:=xlPivotTableVersion10

in bold is the code I need to change as the range will change daily.

Thanks

I have a pivot table with 3 columns that I'm trying to manipulate. One column
has values ranging from 0-10,000, another all over 100,000, an ditto the
third column.

I'm trying to find averages in the latter two based on ranges in the first.
So, the aveage of the latter two based on the first column like this: 0-1000,
1001-2000, 2001-3000, etc. I've been fooling around with for about an hour
with no luck.

Can anyone give me a quick/easy or good way to accomplish this using
pivotables?

Thanks

I am trying to create a pivot table on a new sheet names as 4x4

I am getting following error:
Error : 1004
Reference Not Valid

I am not sure where is the problem. I have genererated this code by using macro recorder and just changed the source and destination of the pivot generation code.

Need your help Guys!!!

Code:

Sub Macro2()
ActiveWorkbook.Sheets("Sheet2").Activate
Dim LastRow1 As Long
With ActiveSheet
LastRow1 = Sheets("Sheet2").Cells(.Rows.Count, "A").End(xlUp).Row
End With
Dim rng As Range

Set rng = Range(Cells(1, 1), Cells(LastRow1, 19))

Sheets.Add.Name = "4x4"
    
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "rng", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="4x4!R3C1", TableName:="PivotTable2", DefaultVersion _
        :=xlPivotTableVersion12
    Sheets("4x4").Select
    Cells(3, 1).Select

Webbug

hello everyone...

I have assumed responsibility for this monthly reporting of services run on a business system. I run periodic queries of the services and each line contains a variety of information about the service including the year and week at which it was submitted.

Our graphs are based on this 52 week pivot table, and every time I have to do this monthly reporting I have to adjust the pivot table range to shift down so that the service data contained within is only for 52 weeks. (if you can imagine, this is very annoying)

My question is - Since I have a "year" and "week" field for each one of my records, would it be possible, using all of the columns containing data, to create a dynamic range that would just shift down to include only the "LAST" 52 weeks worth of data? If I could do this, it would make managing this task much easier.

Thoughts? Thanks in advance, im pulling my hair out over here