Free Microsoft Excel 2013 Quick Reference

Changing pivot table field name Results

I have created a pivot table using a dynamic named range as my source.
The source data consists of 52,416 rows and 10 columns showing:

Membership code
Date
Hour
Day
Week
Month
Term
Year
Checkouts
Checkins

Everything appears as expected when I go through Excel's PivotTable
wizard but the -Sum of Checkins- and -Sum of Checkouts- (which I place
in the data area of the PT) always come up as zeros. The PT seems to
have replaced all the correct data in each cell with a zero!

In addition, I have to manually change the fields to "-Sum of-..." as
opposed to the "-Count of-..." that appears first. It feels as though
this screwy behaviour must have a bearing on why all my data is being
replaced with zeros.

I should add that this has never happened to my other PTs. In fact, I
have a PT in the same workbook with exactly the same columns and layout
with a similar amount of data that works perfectly. I just can't figure
it out!

--
MyrtlePeacock
------------------------------------------------------------------------
MyrtlePeacock's Profile: http://www.excelforum.com/member.php...o&userid=29812
View this thread: http://www.excelforum.com/showthread...hreadid=495192

Hi all, sorry for the length of this post, I'll try to keep things as
simple as possible. I've been given the task of maintaining a
spreadsheet with all of the data showing in pivot tables from a single
source which would be updated every month.
The problem arises when it comes to the shear scale of the worksheet
required, in simple terms the main worksheet shows the following -

* Total sales by company types, regions and product types. (4 pivot
tables in total)
* Average sales by company types, regions and product types. (3 pivot
tables in total)
* Total sales by product name sorted by name and total (2 x 3 pivot
tables)

I then have 9 more copies of this sheet which filter the results as
such -

* Region. (3 regions so 3 sheets)
* Region + company type (3 regions, 2 company types so 6 sheets).

The lazy way that I could produce this is by creating the first sheet
and then just make 9 copies of it and change the Page Fields according
to the relevant region and/or company type but this is arduous and
doesn't really allow much flexibility.

Also the first time I created this report I based all of the pivots
off of a single pivot whose data source was fixed coordinates and of
course now I want to add data I've had to alter them, in fact now to a
data name. The problem is that all of the pivots 'lost' the fact that
they refer to the original pivot so I've had to go through and change
their source manually.

Can anyone think of a way in which I could automatically create all of
my worksheets either through VB or another way?

Thanks in advance.

Hello,

On a pivot table you can elect to hide selected values on any field.
You do this by double-clicking on the field name and highlighting the
relevant items in the "Hide Items" text box that appears.

My question is this: What is the VBA code to detect any of these
selections a User may have made?

Allow me to explain and I will attempt to try an clarify this somewhat
consusing (to me at least) area.

My research so far to discover this code has been on the
"PivotItems(i).Visible" property, but this seems to tell you what
items happen to be visible on the pivot table at the time. This would
change by simply changing the Page File fields or say, by removing a
field from the pivot table alltogether. This is different. This is not
actually the question I am asking.

Because a pivot table is dynamic in nature, I want to be able to
detect, for the purposes of data integrity, whether particular field
values have been suppressed, irrespective of whether that field
happpens to be visible on the table or not. I want to be able to state
to the User that they have elected to suppress certain field values,
whether they would ordinarily appear on the pivot table or not.

I also tried the HiddenFields and VisibleFields properties. But again,
these fields seem to relate to "what you actually see" on the pivot
table at the time. For example, if you were to remove the field off
the pivot, then this property lists all the field items as "hidden",
or perhaps all the fields that are not visible due to the various
filtering effects of the Page Files. (Actually, I could not seem to
easily see the difference between the results of these two
properties).

I hope I have not been two confusing, but to restate: a User can
suppress particular field values by the "double-click method"
described above. Of course, if the field value isn't there for the
particular pivot table view, then it doesn't make any difference.

But it IS important to know that a User has set that particular field
value to "hidden" so that the User can be warned that if the value
ordinarly should show, it won't.

It is a definitly a field click-text box setting a User can set. Just
double-click on a pivot field heading to find it. What is the VBA code
to grab this setting? Thanks,

JonS

Hello experts,
I've reviewed PT0021 Change All Page Fields sample Excel file (for 2007) on
http://www.contextures.com/excelfiles.html#Pivot and am still having trouble
with my complex workbook with several pivot tables/charts. I have found in
this sample file that it works when you choose an explicit value in the
filter, however when you re-set the filters back to 'all' or choose multiple
values, this does not apply to subsequent pivot tables and worksheets. Is
this only possible with explicit values in the main filter or can the code be
modified to work when choosing 'all'?

Also, my workbook contains several worksheets of data, and subsequent
worksheets of various pivot tables for each set of data. The field names
would be consistent across each worksheet of data, however in my ideal world,
I'd like one main set of filters for a main pivot table to control ALL pivot
tables on subsequent worksheets, which feed from a variety of data worksheets
(all in the same workbook). Am I dreaming? So far, Excel pros that I've
solicited help from using the sample file referenced above have not been able
to make this work. Thank you.

Although I realize this isn't the typical method of using a pivot table, I'm
attempting to display text in the data field for my pivot table. The three
source columns consist of 1) a strategy, 2) an activity focused at attacking
that strategy and 3) an organizational contribution that coincides with both.
For example, the source worksheet column name, and input would look
something like this for a single entry:

Source Columns: Strategy Activity Team Contribution
Row Inputs: Safety Study Accrue Data

I realize I can display all three by inputting then in the row area of the
pivottable, but I'd like to input the strategy in the row area, activity in
the column area, and team contribution in the data area to display the
information a bit differently. Is it possible, with a source file input
change, to show the data in the "data field" as text rather than numbers?
Thanks.

-Allan

Brian,

Sorting:
You can control the pivot's sorting by setting the Advanced Options in
the Field Settings for each row-, column- or page field.

AddingRows to source automatically:
Define a name for the table on which the Pivot is based, AND have that
name use a dynamic formula to include allrows/columns.
Name: dnPivotsource
RefersTo: =Offset(Sheet2!$a$1;0;0;counta($a:$a);counta($1:$1 ))

Next: backclick in the PivotWizard to change the Pivot's source
replace the range adddress with the =dnPivotSource 9use [F3] to list
available names.

Now a simple refresh is enough to update the Pivot if you've added some
rows.

cheerz!

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

"brian" > wrote:

> Hello,
>
> I have a pivot table with auto-refresh when the workbook
> is opened. If I add a new row to my source table, the
> pivot table pulls it in, but it puts it at the bottom of
> the pivot table even though I have an "order by" in my
> query.
>
> I was hoping there was an event that could be intercepted
> after the pivot table has done its refresh to run a sort
> macro I created.
>
> Thanks in advance.

Hey, wonder what I might do here. Using excel 2007 to link to a sharepoint
"list". I then "pivot" this list for a variety of reports. However, when I
then, say, the next day, choose Data, Fresh, and then go back to the pivot
table and right click on it to refresh, and see change, I get an error:

"The PivotTable field name is not valid. To create a PivotTable report, you
must use data that is organized as a list with labeled columns. If you are
changing the name of the PivotTable field, you must type a new name for the
field."

And of course, I am not seeing the refreshed information in the pivot table.
Any idea what is going on here? What can I do to keep the Pivot Table
"locked" on the data set, even if I refresh it?

Thanks!

PatK

Hello,

I have a data source with several columns that may contain data I want. For
example, there are multiple stakeholders for each project and the stakeholder
i am interested in may appear in "stakeholder 1" or "stakeholder 2" or
"Stakeholder 3" or so on.

I create a pivot table and add the stakeholder fields to the Report Filter,
but the table "AND"s the columns, so the pivot shows only rows where my
stakeholder is named in all stakeholder fields selected. Can I change this
so the report filter does an "OR" and shows all rows where my stakeholder
appears?

Hi,

I have found that using the following code I am able to corrupt the data in
my pivot table:

On Error Resume Next
ActiveSheet.PivotTables("PTMonthlyAnalystSalesPA") .PivotFields("PA").CurrentPage = ActiveSheet.Range("B1").Value
If Err Then
ActiveSheet.PivotTables("PTMonthlyAnalystSalesPA") .PivotFields("PA").CurrentPage = "(blank)"

What I have found is that values in the PA page field are changed, even
though the actual data source has not. For example, my list used to contain
the name "Alex", but this is replaced with "Steve". In the data source
"Steve" is not referenced in the SA column. If I select Steve, it pulls the
data for Alex!

If you refresh this doesn't make a difference. If you replace the pivot
table with an identical table it returns to normal, until I start running
this code again.

Has anyone encountered this problem and can advise how I can avoid this?

Thanks,
Ra

I have two worksheets in the same workbook: "PIR-DT MTH" and "PIR-D
CAT"
In "PIR-DT MTH", in cell "E3" there is a string for an range value, fo
example "C4:L21"
In "PIR-DT CAT" there is a Pivot Table called "PIR1DTCAT" with Ro
Field "PIR-DTCAT" and Data Field "IR1 DT TIME" (there is no Colum
Field). The Table uses the range value mentioned above to capture th
source data for the table
The range value in "E3" on "PIR-DT MTH" changes by different triggers
the details of which are not important
However, when the value in that particular cell changes, the Pivo
Table in worksheet "PIR-DT CAT" must also update.
So I have a macro, which is supposed to capture the new range value an
update the pivot table.
I tried the very same code on a sample workbook and it works just fine
but when I test on the actual workbook, I get an error:

The call to the function is as follows:
Call Update_PivotTables("IRReports.xls", "PIR-DT MTH", "PIR-DT CAT"
"DTCAT", "1", "E3")

The actual function is as follows (note I put the values that ar
assigned to the internal variables in red for your reference):

Public Sub Update_PivotTables(WkBook As String, SourceWkSheetName A
String, _
ObjWkSheetName As String, InfoType A
String, _
IRNumber As String, RangeInfoCell A
String)

Dim myexcel As Object
Dim myworkbook As Object
Dim sourceworksheet As Object
Dim objworksheet As Object
Dim PivotTableName As String
'Dim PivotRowField As String
'Dim PivotDataField As String
Dim PivotSourceData As String

Set myexcel = GetObject(, "Excel.Application") 'Point to activ
excel application
Set myworkbook = Excel.Application.Workbooks("IRReports.xls"
'Point to the relevant workbook
Set sourceworksheet = myworkbook.Worksheets(SourceWkSheetName
'Point to the relevant worksheet
Set objworksheet = myworkbook.Worksheets(ObjWkSheetName) 'Point t
the relevant worksheet

PivotTableName = "PIR" & IRNumber & InfoType '"PIR1DTCAT"
PivotRowField = "PIR" & IRNumber & "-" & InfoType '"PIR-DTCAT"
PivotDataField = "IR" & IRNumber & " DT TIME" '"IR1 DT TIME"

If sourceworksheet.Range(RangeInfoCell).Value = "None" The
'"C4:L21"
MsgBox "You have to delete the Pivot Table"
Else
PivotSourceData = "'" & SourceWkSheetName & "'!"
sourceworksheet.Range(RangeInfoCell).Value '"PIR-DT MTH'!C4:L21"

objworksheet.PivotTables(PivotTableName).PivotTabl eWizar
SourceType:=xlDatabase, SourceData:= _
PivotSourceData 'THIS IS THE LINE WHERE I GET THE ERROR
ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False
End If

End Sub

The error which occur on the line indicated above states:
The PivotTable field name is not valid. To create a PivotTable report
you must use data that is organized as a list with labeled columns. I
you are changing the name of a PivotTable field, you must type a ne
name for the field.

I don't understand what is happening
Now all of this can be avoided, if I could only figure out how t
instead of putting the range reference like - 'PIR-DT MTH'!$C$4:$L$3
for the Pivot Table, put the value of the cell where the rang
reference string is stored - =TEXT('PIR-DT MTH'!E3
When I tried that I got an error Reference is not vali

--
suzette
-----------------------------------------------------------------------
suzetter's Profile: http://www.excelforum.com/member.php...nfo&userid=707
View this thread: http://www.excelforum.com/showthread.php?threadid=38935

I have a workbook with several worksheets. Each sheet is a snapshot view of
data from any given day or time, and I want to be able to provide the
capability to view any day's data using the same pivot table. I don't want
to combine worksheet data, as each sheet is its own historical record. Each
sheet has the exact same layout -- headings and all. My pivot table is set
up to use columns A through AD ($A:$AD), and I can manually adjust the sheet
I'm querying against by changing the sheet name in the source range field.
However, I want to use a drop down list of all the worksheets in the workbook
to handle this automatically. I figured out - with the assistance of threads
here - how to get a list of all the worksheets, and I have named that range.
Is it possible to somehow use that named range in the source range field for
the pivot table?

Thanks in advance!

I wish to change the default of the fields settings of subtotals from
"automatic" to "none". I can do this manually but it is getting to be
tedious, a simple macro wont do the trick since this presumes that you know
the field name beforehand which seldom is the case. I use Excel 2007.

How can I change this default?

hi,

When I used Pivot Table to consolidate data from different ranges in
different sheets, the created table will have the field buttons labeled as
"Row" & "Column" instead of the actual names of the row and column, for
example Order ID, Company Name.......

I'm wondering whether there is any way that we can change the field button's
caption from, for example "Columns" to Company Name????

Anyone out there has any ideal of doing so???
Please advice, thanking in advance!!!

I'm building out some financial reports where the titles change every month -
consequently changes my calculated field refrences. The sequence or position
of the fields doesn't change & the ideal solution for me would be to
reference the pivot fields with an index number. I've made several attempts
and done quite a bit of searching with no luck. Working with Excel 2007. My
code is below and the code that doesn't work is inside the With block.
Basically, I'm creating the Pivot Table using VBA - inside the with block not
displayed I'm adding Page Fields, Row Fields and quite a few Data Fields.

Dim PTCache As PivotCache
Dim PT As PivotTable
Application.Calculation = xlManual

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:="PVTData")

Set PT = PTCache.CreatePivotTable(TableDestination:=Sheet1. Range("A21"),
TableName:="PivotTable1")

With PT

ActiveSheet.PivotTables("PivotTable1").CalculatedF ields.Add
"BudgetYTD", "=" & PT.PivotFields(71).Name & "+" & PT.PivotFields(72).Name &
"+" & _
PT.PivotFields(73).Name & "+" & PT.PivotFields(74).Name & "+" &
PT.PivotFields(75).Name & "+" & PT.PivotFields(76).Name & "+" & _
PT.PivotFields(77).Name & "+" & PT.PivotFields(78).Name & "+" &
PT.PivotFields(79).Name & "+" & PT.PivotFields(80).Name & "+" & _
PT.PivotFields(81).Name & "+" & PT.PivotFields(82).Name, True

ActiveSheet.PivotTables("PivotTable1").AddDataFiel d
ActiveSheet.PivotTables("PivotTable1").PivotFields ("BudgetYTD"), "Sum of
BudgetYTD", xlSum

End With

Hi all,
I am trying to make an OLAP pivot table filter based on a cell value in Excel 2003. The cell value I want to filter by is essentially a month (but is more complex than that e.g. 2012 - January, 2010 - August).

By recording the macro for manually changing the filter I get the following code

	VB:
	
ActiveSheet.PivotTables("PTD Figures: by Staff").PivotFields( _ 
"[TS Periods].[Periods]").CurrentPageName = _ 
"[TS Periods].[Periods].[Month].&[2012]&[09]" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

From this I get that:
The pivot table name is "PTD Figures: by Staff" - this is determined by the source data
The field in the pivot table is labelled "[TS Periods].[Periods]" - this is determined by the source data
The values I'm trying to change are [2012] and [09] (i.e. the year and month number)
I've set up a form so the user inputs the year and month and this gives me my input variables to change the filter to but for the purposes of this post I'm just interested in getting it working for the month (I can then duplicate the code for the year).

My code currently looks like:

	VB:
	
 FilterPivot() 
     
     
    Dim month As Integer 
     
    month = ActiveWorkbook.Sheets("Input").Range("j5").Value 
     
     
    ActiveWorkbook.Sheets("Prod_Analysis").PivotTables("[PTD Figures: by    Staff]").PivotFields("[TS Periods].[Periods]" _ 
    ).CurrentPageName = "[TS Periods].[Periods].[Month].&[2012]&[month]" 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

in this case the desired value of the month is set in the sheet "Input" and cell "J5". The Pivot table is located in the sheet "Prod_Analysis".

Unfortunately, when I run this I get a Runtime Error '1004': Item could not be found in OLAP cube.

Any help to resolve my issue would be most appreciated.

On a side note I have tried creating a non-OLAP pivot table and the above code seems to work fine.

Thanks,
Heath

I have source data for pivot tables that list the orders by salesperson according to dates. Some of these dates are future dates. One of my pivot table shows their orders according to the year and quarters in that year. I use group data (data being the order date which is then used to show quarters and year). It all works the way it should. I am however ONLY interested in the future dates in this table.

'Group and show details' under Pivot Table menu allows me to specify a start date but it will only accepts a number of decimal entry in the start date field, which means I have to regularly change the start date everytime I want to use the table.

I want to automate this by putting '=today()' into the start date field, which it does not accept. I have also tried the use of 'Define Name' and then using this in the start date field but it wouldn't accept that either. Any suggestions?

Hopefully I can explain this well. I have a spreadsheet with 5 pivot tables - 3 are based off one table and 2 off another. They share the same page fields, "PG" and "PC", with the data exactly the same in both tables (I checked by using the exact formula).

I have this beautiful macro button that will change the both page fields of all 5 pivot tables by referencing two data validation lists. The page fields change just like they should and the pivot tables update the information the first time it is run. The problem is if you try to select a different data set from the page fields and then run the macro, it does not perform correctly. The page fields change, and the pivot tables try to update, but they return either no results when there should be some or else incorrect results.

Below is the code for my macro button. The range names are the cells that the two validation lists are located in.

Sub
ChangeAllPivotHeaders()
Dim SPgField As String, SPgField2 As String

SPgField1 = Range("PG_Name")
SPgField2 = Range("PC_Name")
Dim Pt As PivotTable
Dim Wsht As Worksheet
On Error Resume Next
For Each Wsht In ThisWorkbook.Worksheets
For Each Pt In Wsht.PivotTables
Pt.PageFields(1).CurrentPage = SPgField1
Pt.PageFields(2).CurrentPage = SPgField2
Next Pt
Next Wsht

End Sub

Any ideas? Hopefully this makes sense without having the file as it is large and all confidential data that would have to be overridden.

Thanks for your thoughts!
N

Hi All,
I have a sheet of data, and a pivot table on another sheet that uses the
data. When I change the name of a data field, the data field disappears from
the pivot. I would have thought that the name would just update. The new
data name is in the field list, but having to go into the field list and
drag it out to the pivot is not very practical.

Has anyone noticed this issue? Is there a way to make this work?

Essentially the pivot is filled with place holder data names (Data1, Data2,
etc.). The real names are filled in later after the workbook is distributed
around the company, so the manipulation of the pivot is out of my hands, and
multipled many times over (over 50 workbooks go out).

Thanks for any advice,

KB

I have a pivot table that accesses an Analysis Services cube. I have a
customer dimension structured as follows:

Level: Account
Key: Account
Name: Account + Region + Description

The Problem
Office Excel 2003 users select multiple accounts from the dimension they
have dragged onto the page field of the pivot table and save speadsheets onto
their local hard drives and enable automatic refresh against the cube. When a
customers region changes, they get a message box stating that the olap item
is not found in the database and the account number(s) they have selected are
no longer selected, and sometime, only the All Customers value is selected.

The Question
Is there anything I can do to prevent changing customer names from being
de-selected from the customer dimension drop-down in the pivot table?

Thanks!

I have a pivot table in which "PO or Plan" is one of the row items. Currently, due to Alphabetical order, cells that say "Planned Order" are first, with "PO11123..." following in chronological order. In the GUI, you can highlight the cell, drag is to the bottom of the list, and the rest follow, but I can't figure out how to do it in the code.

Recording a macro shows it being given a static position, which won't work for me since the pivot table source data changes daily. Any ideas? (see attached image for screenshot)

Sub CreatePivotTable()
    Dim WSD As Worksheet
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range
    Dim FinalRow As Long

    Set WSD = Worksheets("PO")
    Dim WSR As Worksheet
    
    ' Delete old pivot table worksheet and create a new one
    Application.DisplayAlerts = False
    Sheets("OpenQty Table").Delete
    Application.DisplayAlerts = True
    Sheets.Add.Name = "OpenQty Table"
    ActiveSheet.Move after:=Worksheets(Worksheets.Count)
    Sheets("PO").Select

    ' Define input area and set up a Pivot Cache
    FinalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    FinalCol = WSD.Cells(1, Application.Columns.Count). _
        End(xlToLeft).Column
    Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
        xlDatabase, SourceData:=PRange.Address)

    ' Create the Pivot Table from the Pivot Cache
    Set PT = PTCache.CreatePivotTable(TableDestination:=Worksheets("OpenQty Table").Range("A2"),
TableName:="PivotTable1")

    ' Turn off updating while building the table
    PT.ManualUpdate = True

    ' Set up the row and column fields
    PT.AddFields RowFields:=Array("Prefered Supplier", "M-Spec", "PO or Plan"),
ColumnFields:="DueDate"
    

    ' Set up the data field
    With PT.PivotFields("OpenQty")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
        
    End With

    ' Ensure that you get zeroes instead of blanks in the data area
    PT.NullString = "0"

    ' Calc the pivot table to allow the date label to be drawn
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
    'Focus on pivot table worksheet to allow DueDate grouping by month
    Sheets("OpenQty Table").Select
    
    ' Group DueDate by month
    Worksheets("OpenQty Table").Range("D2").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, False)

    ' Calc the pivot table
    PT.ManualUpdate = False
    PT.ManualUpdate = True
    
    

End Sub



No luck finding an answer? You could always try Google.