I am trying to create a pivot table from data I have in one sheet however I only need certain columns of the data. For example below I have 4 columns of data however I only want to have the data for columns "Date", "Country" and "Passengers" in my pivot table field list:
Date Country House No. Passengers
01-Jan-09 Japan 15 10
02-Jan-09 Guildford 1 11
03-Jan-09 Hever 89 12
04-Jan-09 Japan 18 13
01-Jan-09 Guildford 26 14
Any ideas?
--
James.
Just use the entire table of data. You can configure the Pivot Table to use only the fields that are needed. Fields in the Pivot Table do not automatically display or calculate until you perform an action on them.
Hi Palmetto
Thank you very much for getting back to me. The problem I will have though is that all the columns will show in my pivot table field list i.e. be available as options to select from which I don't want them to be. Does this make sense?
OK. I see what you mean now.
There is no way that I am aware of to hide items in the field list. I think you will have to work with a subset of the data that excludes the columns you dont want them to access.
But this may not be feasible if those columns contain data needed for calculations in the the Pivot Table.
Once the PT is setup you can apply code such that the Field List is Disabled when PT first updated (refreshed etc...) - the same can be done with the PT Wizard which pre-07 could be used to alter the layout also, eg:
Not sure if this is what you want or not... obviously you could add Admin functionality such that Boolean Enabled status is dependent say upon Environ("username") being = to your own credentials (ie if you (true) then features enabled else (False) they are disabled)
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) Application.EnableEvents = False With Target .EnableFieldList = False .EnableWizard = False End With Application.EnableEvents = True End Sub
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
To do it simply, you would just need to rearrange the data so that the 4 columns make up a contiguous range of data.
Everyone who confuses correlation and causation ends up dead.
Hi all
Thanks for the responses.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks