Change Pivot Table Field List

I'm looking for an example of how to turn on/off certain fields in a
pivot table.
0
himmelrich (30)
1/14/2009 7:46:14 PM
excel 39879 articles. 2 followers. Follow

3 Replies
459 Views

Similar Articles

[PageSpeed] 40

What version of Excel do you have? I am using Excel 2007 and here's what I 
do.

Right click anywhere in the pivot table and say "Show Field List". From 
there I can check or uncheck fields I want or do not want to show.

If you right click the pivot table again you can hide that list.

"S Himmelrich" <himmelrich@gmail.com> wrote in message 
news:a54c0a1a-476c-4682-bc5f-95620be56ef6@f20g2000yqg.googlegroups.com...
> I'm looking for an example of how to turn on/off certain fields in a
> pivot table. 


0
shawn2884 (1)
1/14/2009 8:29:02 PM
I have four pivot views, D Rank, S Rank, B Rank and O Rank.  I know
how to add an items as illustrated below below, however removing it
errors when it's actually not there...how do I avoid this is my
question?

ActiveCell.FormulaR1C1 =3D "Data Completeness Rank"

' if the Pivot field is not showing I error out just below
    ActiveSheet.PivotTables("PivotTable1").PivotFields("S
Rank").Orientation _
        =3D xlHidden
    ActiveSheet.PivotTables("PivotTable1").PivotFields("B Rank"). _
        Orientation =3D xlHidden
    ActiveSheet.PivotTables("PivotTable1").PivotFields("O Rank"). _
        Orientation =3D xlHidden


ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("DC Rank"), "Sum of DC Rank", xlSum



On Jan 14, 3:29=A0pm, "shawn" <sh...@peppermint-bay.com> wrote:
> What version of Excel do you have? I am using Excel 2007 and here's what =
I
> do.
>
> Right click anywhere in the pivot table and say "Show Field List". From
> there I can check or uncheck fields I want or do not want to show.
>
> If you right click the pivot table again you can hide that list.
>
> "S Himmelrich" <himmelr...@gmail.com> wrote in message
>
> news:a54c0a1a-476c-4682-bc5f-95620be56ef6@f20g2000yqg.googlegroups.com...
>
>
>
> > I'm looking for an example of how to turn on/off certain fields in a
> > pivot table.- Hide quoted text -
>
> - Show quoted text -

0
himmelrich (30)
1/14/2009 11:22:37 PM
You could add a line above that section:
   On Error Resume Next

After that section use another line such as:
  On Error GoTo errHandler
substituting the name of your error handler.

S Himmelrich wrote:
> I have four pivot views, D Rank, S Rank, B Rank and O Rank.  I know
> how to add an items as illustrated below below, however removing it
> errors when it's actually not there...how do I avoid this is my
> question?
> 
> ActiveCell.FormulaR1C1 = "Data Completeness Rank"
> 
> ' if the Pivot field is not showing I error out just below
>     ActiveSheet.PivotTables("PivotTable1").PivotFields("S
> Rank").Orientation _
>         = xlHidden
>     ActiveSheet.PivotTables("PivotTable1").PivotFields("B Rank"). _
>         Orientation = xlHidden
>     ActiveSheet.PivotTables("PivotTable1").PivotFields("O Rank"). _
>         Orientation = xlHidden
> 
> 
> ActiveSheet.PivotTables("PivotTable1").AddDataField
> ActiveSheet.PivotTables( _
>         "PivotTable1").PivotFields("DC Rank"), "Sum of DC Rank", xlSum
> 
> 
> 
> On Jan 14, 3:29 pm, "shawn" <sh...@peppermint-bay.com> wrote:
> 
>>What version of Excel do you have? I am using Excel 2007 and here's what I
>>do.
>>
>>Right click anywhere in the pivot table and say "Show Field List". From
>>there I can check or uncheck fields I want or do not want to show.
>>
>>If you right click the pivot table again you can hide that list.
>>
>>"S Himmelrich" <himmelr...@gmail.com> wrote in message
>>
>>news:a54c0a1a-476c-4682-bc5f-95620be56ef6@f20g2000yqg.googlegroups.com...
>>
>>
>>
>>
>>>I'm looking for an example of how to turn on/off certain fields in a
>>>pivot table.- Hide quoted text -
>>
>>- Show quoted text -
> 
> 


-- 
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

0
dsd1 (5911)
1/17/2009 3:57:33 AM
Reply:

Similar Artilces:

CRM v3.0
We have a customer who would like to change the font color of the Read Only fields. When a read only field is presented, they complain that the font color is too light. Where can I make this change - I'm pretty sure there is a CSS somewhere that I can edit. -- Carroll Little ...

Automated changes of Outlook contact fields
What software / utilities / code etc. are available that would allow me to adjust some existing data in my Outlook 2003 contacts? For example, I have many "web page address" fields that need to be changed from e.g. http://subway.com to http://www.subway.com and also many "city" fields that need to be capitalized e.g los angeles to Los Angeles. Thanks in advance for any replies, Miner2049er. After some research, I believe I have to write a Visual Basic script to do this. Not knowing that language, does anybody know of a script that can do what I'm asking, or at l...

How do you add text to custom columns created thru the Field Chooser pls?
Hello, If I create a custom text column via Field Chooser - New, how can I enter text into it afterwards? I'm looking to add an "annotation" column and not have it sent if/when I forward or reply to the message. Outlook 2003 with Exchange 2003. Thanks, - Alan. If you directly want to fill it out in the message list you'll have to enable "allow in-cell editing" for your view. The technique would be similar to; http://www.msoutlook.info/question/150 -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://ww...

online backup ideas? Tired of changing tapes
I manage a network with a small business server and a secondary w2k3 app server. I am ready to stop wasting time swapping tapes for backups everyday and want to know if _you_ have tried any online backup solutions, and which, if any you would recommend. I would like it to be exchange aware and to do a complete backup including system state, etc so that I could restore a crashed server from it if need be. I am additionally curious if any of the online backup programs can be set to simultaneously update a single local backup copy store on say, an external harddrive - just fo...

Updating Global Address List
Our global address book needs to have many users updated due to phone number/address changes etc. and my help desk person is bugging me to allow some secretary access to do the updates. I do not know of any secure way to allow someone to do these updates. Can anyone point me in the right direction or am I right to tell the lazy brat it's his job. On Fri, 4 Feb 2005 07:19:02 -0800, "Maive72" <Maive72@discussions.microsoft.com> wrote: >Our global address book needs to have many users updated due to phone >number/address changes etc. and my help desk person is b...

A field on a query has a value in it I'd like to use to create tot
Cost: Proceeds: G/L St G/L LT 30,000 31,000 $1,000 25,100 24,095 ($905) 4,000 5,000 $500 $500 In the G/L St & G/L Lt field, I would like to pull out the cost/proceeds values to create a field that I can run a cost or proceed total on. I have done an IIf function that works in pulling out the values but in the report footer when I try to sum the data, it doesn't work. FYI: The iif function is this: Iif([g/l s...

Can't change gridlines colour a second time
Hi, I changed my chart gridlines to white to make them invisible and I now want to change them back but I can't find them! When I scroll over the chart I can only click on the plot area, not the white grid lines. Can someone tell me how to change the colour again? Kind regards, Alison "AlisonB" <alison.broadley@ntlworld.com> wrote in message news:1147361275.614198.57790@y43g2000cwc.googlegroups.com... > Hi, > > I changed my chart gridlines to white to make them invisible and I now > want to change them back but I can't find them! When I scroll over ...

Global Address List per Mail Domain
Hi! I host multiple domains on my Exchange 2003 server. I created new Global Address Lists in ESM for each mail domain: for example GAL for abc.com, and GAL for xyz.com. I want that the users who belong to domain abc.com should see ONLY and ONLY their own GAL, NOT the GAL for xyz.com. And users who belong to domain xyz.com should see only and only their own GAL. How can I realize it? Where should I restrict the permissions? Which permissions for whom? Best Regards Mustafa Have a look at this KB article How to Manage Address Lists When You Host Virtual Organizations http://support.micr...

Pivot Table Help #2
- Automaticly Refreshing Pivot Table Data I have a made a macro called "depsub" that gathers sales data from each one of our sales departments, compiles it onto one page, sorts it and SUBTOTALs the data by employee and department. I also have a PivotTable that needs to use this same data. PivotTables can not be generated from subtotaled data. So I have to nest a macro within my "depsub" macro that generates a PivotTable prior to the data being subtotaled. Problem is every time I try to do this the macro errors out and stops when I try to creat the pivot table. Here i...

Auto color change
I would like to know if there is a way to have a cell automatically change colors; here is my scenario. I track dates of several training sessions that are a requirement for our personnel. Some need to be reaccomplished every 12 months, 15 months and 30 months. When someone's last accomplished date is within 30 days of expiring, I'd like the "due date" cell to change colors. format>conditional format>formula is>put in your formula>format as desired. -- Don Guillett SalesAid Software donaldb@281.com "Addisonbc" <Addisonbc@discussions.microsof...

Replacing individual characters in a field to something else.
I have a field with names that is pulled from a main frame database. These names have the last name first, then a comma, then the first name. I want to get rid of the commas. I can do this easily enough with the edit replace. But this table will be constantly updated from the main frame Db. I want to build it as part of an automatic process when running the update. I though of using an update query, using a wildcard to find the commas, but cannot figure out how to update just the character. It wants to update the untire contents of the field. any thoughs? -- Message posted ...

Various Outlook 2003 issues
Hello, I've been using OL2003 w/ WinXP since it was released. I used various other versions of OL for many years before that. In all that time, I have never experienced the issues that have come up lately. I'm hoping someone can help me resolve them. 1. In the midst of composing a new message or replying to a message, the message format will change from HTML to plain text all by itself. This started happening with replies to one person specifically but has happened with increasing frequency and probably occurs with more than 50% of all ougtoing messages now. When I switch the message...

Formatting changes when copying or inserting text from other word
Hi All, I have 2 documents... I need to combine the top portion of the first and bottom portion of the second... they both have their own formattings. When I combine them (Copy/paste or insert pages) the formating of the one I copy or insert changes. I want the formatting of each of the decuments remain as they were. As if I am combining 2 pdf documents. How can I do this? I am using MS Word 2000. Thank you Be sure that the two documents don't have any Style names in common (unless styles with the same name are absolutely identical in both documents). And put a S...

Need to change font specs on Signature
When I copy and paste the signature I need, the font, size and color change and I can't find where to change them. Does anybody know? -- .. . . . . . . . . . . . . . . . . . . Rubi Wiswall Project Manager/Client Services 610-505-8504 .. . . . . . . . . . . . . . . . . . . Web-Wis-dom 3513 Bowman Street Philadelphia PA 19129 www.web-wis-dom.com If you want a specific font in a signature, you need to include that in the signature's HTML file. Then use the Insert Signature for it. There's a quirk in WLM. A new message will let you select a signature v...

Sorting & Grouping ... Change column (field) Titles
I was wondering if my previous post ... same subject got lost? Thanks, Bob ...

Changing RecordSource Causes A Reload of Sub Form Destroying References
Apologies for posting in two newsgroups but after I originally posted it in forms I realized this should have been posted here (formscoding) instead. Access 2007 This only happens on some machines. It has occurred on a PC running Windows XP SP3 and a MAC running Windows. I have been unable to reproduce it on other PCs running Windows XP SP3, Vista or Windows 7. On the machines that have the problem it always occurs. I have a form with a sub form control. The control's SourceObject is changed depending on the function required. On the forms that is used in the control is a...

show 2nd field when combo box choice is made
Greetings: I have a db for clients and their purchases. In my attempt to further normalize my tables, I have created a new table to list the products clients can purchase. So now i have 3 tables. One has the client info, the 2nd has the individual orders (ID, client foreign key, product (stores combo box info) , order date, quantity ordered and a price number field to hold the info this question is about) and the 3rd has the list of products and the price of each product. I have a form to enter the client info and a subform to enter each client's order info. The order subform ...

Table relationship
Hello, I have a table, USERS, where I hold the Username, Password and Email of all users. Each user can be a student or a professor. For a student I need 3 more fields: NAME, BIRTHDAY and CITY For professor I need 4 more fields: NAME, CV, CITY, PHONE. Should I add all these fields to USERS table and leave the ones not used for a Professor or Student empty? Or maybe use another tables? What would be the better approach for this? Thanks, Miguel I have a table, USERS, where I hold the Username, Password and Email of all users. Each user can be a student or a profe...

Unable to link to split table in Access 2002
The following code works in Access 2000, but does not work in Access 2002. Error is ... Method 'Connection' of object '_Current Project' failed. Can you help me fix this? I'm using Windows XP on both computers. Thanks Private Sub Form_Open(Cancel As Integer) On Error GoTo err_form_open 10 'Print CurrentProject.Connection: Stop Dim rst As New ADODB.Recordset 20 rst.Open "qryPropertyProfile", CurrentProject.Connection, _ adOpenKeyset, adLockOptimistic 'Debug.Print rst!PropertyCode txtTempPropCode.DefaultValue = rst!Pro...

how to change text of a part of a sentence automatically
Hi, I would like to know, how can I select part of a sentence Range object and change it's text automatically. Hi, The following macro demonstrates the simplest way to change part of the sentence in which the cursor is located. Note that it doesn't do anything if the search text is not found. Sub ReplaceTextInSentence() Dim OldText As String Dim NewText As String OldText = InputBox("Type the text that you want to replace.") NewText = InputBox("Type the new text.") With Selection.Sentences(1).Find .Text = Ol...

Sorting pivot table by specific field (column)
Hey guys (and girls), Anyone know how to sort a pivot table by a specific field (not the total sum of the fields)? For instance, if I have 5 years of data 2002-6 and 20 countries. If I make a pivot table of these I get 21 rows (the countries + total) and 6 columns for years (5 years of data + total). If I use the Field Settings --> Advanced --> Sort by field the Pivot table (PT) will be sorted by the sum of the different fields. What if I don't want this, but rater want to sort it by for instance year 2003. How do I do that? Anyone have a nice and clever solution? Much appreciated...

change comment author
i need to change the author for all the comments in a number of spreadsheets. the only way i can see to do this is to loop through each comment, save the comment text, delete the comment, then add it back as a new comment -- which will assign the current Application.UserName to the author property. the problem is that all the formatting and positioning of the comment is lost, which makes this scheme totally useless. is there any way to change the comment author WITHOUT losing all the formatting? lee Lee, have a look here and see if this will do what you want http://www.contextures.com/xl...

Prevent change of color scheme in Pivot chart
I set up a Pivot-chart and modifying the color scheme. As soon as I make a selection in one of my page fields the color scheme reverts back to default (automatic) and I have to go in and do all the modifications again. Is there a way to prevent the pivot chart to change the color scheme? {I am using Excel 2002 SP3} Thanks, Bernd Loss of formatting is a known problem with pivot charts. There's information in the following MSKB article, suggests recording a macro as you apply the formatting: XL2000: Changing a PivotChart Removes Series Formatting http://support.microsoft.com/...

Blank field problem at access 2007
Hi, I have a query with 3 columns. 1st and 2nd columns are from a table that includes numbers. 3rd column is for summation of 1st and 2nd columns. I have no summation if a field is blank. For example, query gives a result something like below: 1st_______2nd________3rd 10________20_________30 3_________5__________8 Blank______7__________Blank (must be 7 ???) 15________Blank_______Blank (must be 15 ???) How can I solve this problem? Thanks a lot in advance. Use Nz() around each of the numbers. Presumably you have a calculated query field that looks like this: Col3: [Col1] + [Co...

Changed Font on Reply messages
When I reply to an outlook e-mail from one our internal stations, within the office, my reply font color is changed to a light yellow that cannot be read. This means going into format and changing the color so it can be read. I cannot find where I can change the stations program to correct this. ...