Excel Name Trick #5: Names for Pivot Tables & more...

Hi all,

I'm having an issue regarding Pivot Tables and my column header names. The problem is that my column header names can change at any time and the user can do this at any time. However, my pivot tables are obviously designed to use that specific column with a specific header name when you first create it.

For example, if I have 3 cells: B1 with the value = "Year 2010", C1 = "Year 2011", and D1 = "Year 2012". The pivot table will use those titles for the actual pivot table produced ("Year 2010", "Year 2011", etc)...

The issue is when the user can change B1, C1, and D1 later on... perhaps they can shift it to B1 = "Year 2011", C1 = "Year 2012", and D1 = "Year 2013".

If they do this and refresh the pivot table, the pivot table won't work. Is there anyway to have the Pivot Table understand what's going on (that just a simple name had changed for that column, but still to get data from that column)?

Thank you all.

I have about 300 rows of data...one column includes salespersons names in the following format: first name last night (i.e. tim smith). I have moved this into a pivot table with some other data but I now need to sort by LAST NAME in the pivot table. Aside from changing the format of everyone's name (300 entries which would take forever) so that it is "Smith, Tim" in each cell, is there a shortcut around this? Any help would be muchly appreciated.

Thanks,
SF

I have about 300 rows of data...one column includes salespersons names in the following format: first name last night (i.e. tim smith). I have moved this into a pivot table with some other data but I now need to sort by LAST NAME in the pivot table. Aside from changing the format of everyone's name (300 entries which would take forever) so that it is "Smith, Tim" in each cell, is there a shortcut around this? Any help would be muchly appreciated.

Thanks,
SF

Is there a way to categorize data by named ranges of the column headings
rather than individual column headings? I need to be able to pivot between
certain ranges rather than eliminate or add one column at a time. Or perhaps
someone can suggest a different way to do it. I need to create 1 chart to
pivot between the first 5, 7, 9 and 11 data columns.

I noticed that when creating a pivot table whilst recording a macro, the code will automatically name the Pivot table "PivotTable1" and so on.

For ease of reference when referring back to my code, is there a way to rename the Pivot Table? So for example, if I wanted to change the name of PivotTable1 to "Membership", how would I do that?

Thanks

Morning,

Is there a way to force a pivot table to repeat row names?

As it is now I end up seeing something like:

-------------
John North
South
East
West
--------------
I would like it to repeat the name in column A down each row.

As it stands, I've resorted to adding an extra column, and a formula along the lines of =IF(ISBLANK(B2),A1,B2), then hiding the name column entirely.

Hello All,
I have created a pivot table which hide some items and group others in 5 different groups.
The problem I am having is that when of the pivot items is missing from the data or there is item in excess from what I had programed the table doesn't work properly.

I have about 53 items (as above) that are grouped into 5 groups.I wont put them all here because i think this would be too big a post.
I do the same for the 5 groups, basically the way they are grouped is by first organizing them into positions and then i select the colums and group

So far I have created a "blank data set where it has 1 example of all the transactions added to my full data bank, so my data will always have at least one transaction per type of transaction and my pivot table macro will work.

However, when a new type of transaction that I havent programmed appears in my data the method of grouping the data by range will not work as there will be more ranges than i programmed for.

If a new item type is available I would like to set it invisible.

Is there a way of grouping the items by their names and even when a type of item is not present the table will still run?
I was thinking perhaps a code where I could say something like:

VB:

Allpivot items.visible = False 

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





is it possible?

So if I would set all invisble first, and them set visible the ones I want and grouping them by range it would solve my problem by now. But the Ideal would be something where I could name the items that I wanted to include in each group. and them put if error go to next.

Anyone can help?


Below the full code if you are interested.
VB:

 'Create PivotTable
 
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
ActiveSheet.UsedRange).CreatePivotTable _ 
TableDestination:="", TableName:="TXM", DefaultVersion:= _ 
xlPivotTableVersion10 
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
ActiveSheet.Cells(3, 1).Select 
ActiveSheet.PivotTables("TXM").PivotFields("ACCOUNT").Subtotals = Array _ 
(False, False, False, False, False, False, False, False, False, False, False, False) 
ActiveSheet.PivotTables("TXM").PivotFields("ACCOUNT_NAME").Subtotals = _ 
Array(False, False, False, False, False, False, False, False, False, False, False, False) 
ActiveSheet.PivotTables("TXM").PivotFields("PURP_CD").Caption = _ 
"Transactions" 
ActiveSheet.PivotTables("TXM").AddFields RowFields:=Array("ACCOUNT", _ 
"ACCOUNT_NAME"), ColumnFields:="Transactions" 
With ActiveSheet.PivotTables("TXM").PivotFields("CASH_AMT IN USD") 
    .Orientation = xlDataField 
    .Caption = "Sum of CASH_AMT IN USD" 
    .Function = xlSum 
End With 
ActiveWorkbook.ShowPivotTableFieldList = True 
 
 'Select which Items will be invisible in order to group them by selecting range.
 
With ActiveSheet.PivotTables("TXM").PivotFields("Transactions") 
    .PivotItems("BONDLENDCLOSBORR").Visible = False 
    .PivotItems("BONDLENDOPENEND ").Visible = False 
    .PivotItems("CMS     WITHDRAW").Visible = False 
    .PivotItems("CUSTODY BLCKENTL").Visible = False 
    .PivotItems("CUSTODY BLOCK   ").Visible = False 
    .PivotItems("CUSTODY REVBLKEN").Visible = False 
    .PivotItems("CUSTODY UBLCKENT").Visible = False 
    .PivotItems("CUSTODY UNBLOCK ").Visible = False 
    .PivotItems("DELIVERYDOMFOP  ").Visible = False 
    .PivotItems("DELIVERYNORMAL  ").Visible = False 
    .PivotItems("DELIVERYREDEMPT ").Visible = False 
    .PivotItems("FUND    TRANSFER").Visible = False 
    .PivotItems("RECEPTN NORMAL  ").Visible = False 
    .PivotItems("TRANSFERFOPIN   ").Visible = False 
    .PivotItems("TRANSFERFOPOUT  ").Visible = False 
    .PivotItems("TRANSFERNORMAL  ").Visible = False 
End With 
 
 ' Set the position of the Items on the table in order to group them
 
 'Credit operations
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"FEES    REBATES ").Position = 1 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"FUND    TRANSFER 1X").Position = 2 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"FUND    CREDIT  ").Position = 3 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"TAXATIONADJUST 10").Position = 4 '
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"TRANSFERCASHIN  ").Position = 5 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"FEES    VARIOUS 10").Position = 6 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"FEES    SAPBILL 10").Position = 7 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"TAXATIONCREDIT  ").Position = 8 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"UNKNOWN 2 10").Position = 9 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"CMS TOPUP 10").Position = 10 
 
 'Corporate events
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"CONSENT CREDIT  ").Position = 11 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"DIVCHOI SALEODD ").Position = 12 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"DIVCHOI CREDIT  ").Position = 13 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"DIVPMNT CREDIT  ").Position = 14 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"BONUSAUTSALEODD ").Position = 15 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"DIVPMNT REDRESS ").Position = 16 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"REDPARD CREDIT  ").Position = 17 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"INTERESTCREDIT  ").Position = 18 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"DIVPMNT ADJUST  ").Position = 19 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"FUND    CREDIT  C").Position = 20 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"INTERESTREVERSAL 10").Position = 21 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"INTERESTADJUST 10").Position = 22 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"INTERESTCOMPENS 10").Position = 23 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"REINVESTADJUST  ").Position = 24 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"INTERESTREDRESS 10").Position = 25 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"LIQUIDATCREDIT  ").Position = 26 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"OFFEXCHGCREDIT  ").Position = 27 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"OFFPURCHCREDIT  ").Position = 28 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"OFFREPURCREDIT  ").Position = 29 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"OPTCONV SECSALE ").Position = 30 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"REDFINALCREDIT  ").Position = 31 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"REDFINALREDRESS ").Position = 32 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"REINVESTSALEODD ").Position = 33 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"REVSPLITCREDIT  ").Position = 34 
 
 'Purchases
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"PURCHASEEOC     ").Position = 35 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"PURDOM  ITALY   ").Position = 36 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"PURCHASENORMAL  ").Position = 37 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"PURDOM  AKV     ").Position = 38 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"PURDOM  USA     ").Position = 39 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"PURDOM  VARIOUS ").Position = 40 
 
 'Sales
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"SALE    EOC     ").Position = 41 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"SALE    NORMAL  ").Position = 42 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"SALEDOM VARIOUS ").Position = 43 
 
 'Debt Operations
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"CMS     TOPUP   ").Position = 44 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"UNKNOWN 2       ").Position = 45 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"INTERESTADJUST  ").Position = 46 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"INTERESTCOMPENS ").Position = 47 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"INTERESTREDRESS ").Position = 48 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"TAXATIONADJUST  ").Position = 49 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"FUND    TRANSFER").Position = 50 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"FEES    VARIOUS ").Position = 51 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"OFFPURCHREVERSAL").Position = 52 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions").PivotItems( _ 
"REINVESTREVERSAL").Position = 53 
 
 '############################
 ' Group Items into categories
 '############################
 
 'Credits
Range("C4:L4").Select 
Selection.Group 
Range("C4").Select 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions2").PivotItems( _ 
"Group1").ShowDetail = False 
 'Interests
Range("D5:AA5").Select 
Selection.Group 
Range("D4").Select 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions2").PivotItems( _ 
"Group2").ShowDetail = False 
 'Purchases
Range("E5:J5").Select 
Selection.Group 
Range("E4").Select 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions2").PivotItems( _ 
"Group3").ShowDetail = False 
 'Sales
Range("F5:I5").Select 
Selection.Group 
Range("F4").Select 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions2").PivotItems( _ 
"Group4").ShowDetail = False 
 'Debts
Range("G5:AB5").Select 
Selection.Group 
Range("G4").Select 
ActiveSheet.PivotTables("TXM").PivotFields("Transactions2").PivotItems( _ 
"Group5").ShowDetail = False 
 
 'Delete totals from table
With ActiveSheet.PivotTables("TXM") 
    .ColumnGrand = False 
    .RowGrand = False 
End With 

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



Thanks in advance for the help!

Is there any way to change the name of the total columns that a pivot table produces? I've ended up with a table that has a column "Total Total Cost", which is more than a little bit silly...

hello there,

I have a data set with dates that I chart grouping by month and year
however it sets month names in "mmm" format, and in the default language (spanish) for my computer. Is there a way to change that to month number (1,2,3..), or to english abreviations at least, so that the chart is more international-oriented?

I tried changing format of everything, from axes to field settings to cell format... nothing worked

thanks a lot!

Hello Everyone

I am trying to find the data source for a pivot table. And when I click on "change data source" tab, it directs me to a single cell. And I can make new pivot table by using that single cell ("a1") as data source.
Can anyone explain to me how this works?

PS: there is no Macro inside, no hidden worksheets

Thanks a million