Pivot Table Updates Issue

My Data had a coloum containing stuff like

Marketing Department
Sales Department
Shipping Department

I built a bunch of pivot tables on this data.  Then I changed the data 
to read:

Marketing Dept
Sales Dept
Shipping Dept

and performed a Refresh Data.

Now, on the filter pulldowns on any of the Pivot tables, I see
Marketing Department
Sales Department
Shipping Department
Marketing Dept
Sales Dept
Shipping Dept

The first three option no longer exist in any of the data, and I do not 
want them in the list.  How to fix this problem without recreating and 
reformatting 38 seperate Pivot tables?

Thanx
0
phil7437 (9)
8/22/2007 6:50:18 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
555 Views

Similar Articles

[PageSpeed] 7

There are instructions here for clearing old items from a pivot table:

      http://www.contextures.com/xlPivot04.html


Phil Smith wrote:
> My Data had a coloum containing stuff like
> 
> Marketing Department
> Sales Department
> Shipping Department
> 
> I built a bunch of pivot tables on this data.  Then I changed the data 
> to read:
> 
> Marketing Dept
> Sales Dept
> Shipping Dept
> 
> and performed a Refresh Data.
> 
> Now, on the filter pulldowns on any of the Pivot tables, I see
> Marketing Department
> Sales Department
> Shipping Department
> Marketing Dept
> Sales Dept
> Shipping Dept
> 
> The first three option no longer exist in any of the data, and I do not 
> want them in the list.  How to fix this problem without recreating and 
> reformatting 38 seperate Pivot tables?
> 
> Thanx


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

0
dsd1 (5911)
8/22/2007 7:24:42 PM
Worked perfectly.  thank you.



Debra Dalgleish wrote:
> There are instructions here for clearing old items from a pivot table:
> 
>      http://www.contextures.com/xlPivot04.html
> 
> 
> Phil Smith wrote:
> 
>> My Data had a coloum containing stuff like
>>
>> Marketing Department
>> Sales Department
>> Shipping Department
>>
>> I built a bunch of pivot tables on this data.  Then I changed the data 
>> to read:
>>
>> Marketing Dept
>> Sales Dept
>> Shipping Dept
>>
>> and performed a Refresh Data.
>>
>> Now, on the filter pulldowns on any of the Pivot tables, I see
>> Marketing Department
>> Sales Department
>> Shipping Department
>> Marketing Dept
>> Sales Dept
>> Shipping Dept
>>
>> The first three option no longer exist in any of the data, and I do 
>> not want them in the list.  How to fix this problem without recreating 
>> and reformatting 38 seperate Pivot tables?
>>
>> Thanx
> 
> 
> 
0
phil7437 (9)
8/22/2007 8:44:12 PM
Reply:

Similar Artilces:

Can't save existing .trc file to table Profiler 2008
Hello, I'm new to SQL Profiler. I've created a .trc file and saved it to disk. I can open the .trc file but I want to now save this existing file to a table. When I go under Save As all the options are grayed out. I have another .trc file and when I open that file and select Save As all the options are available. Don't see any differences between the files. If I create a new trace I can save it to a table. Don't understand why when I open that one trace all the Save As options are grayed out. Any help would be appreciated. Gi Hi Gi, You probably have a la...

CRM 4.0 queue issue
Hi, I've run into this in CRM 3.0 (and honestly, I don't know what I did to fix it). But now it's happening in CRM 4.0. I have a queue, and a user with an email address. I want all email that is sent to this user to also appear in the CRM queue. I have it working, it's just that I'm getting everything twice. Once as an email from the original sender, and once from the recipient to the recipient, with the original email as an attachment. For example, kbriscoe@domain.com sends an email to ITSupport@domain.com (Our queue's email address is ITSupport@domain.com). T...

Update
What is Happening? This message is to inform you that Microsoft will soon begin discontinuing newsgroups and transitioning users to Microsoft forums. Why? As you may know, newsgroups have existed for many years now; however, the traffic in the Microsoft newsgroups has been steadily decreasing for the past several years while customers and participants are increasingly finding solutions in the forums on Microsoft properties and third party sites. This move will unify the customer experience, centralize content, make it easier for active contributors to retain their influence, mitiga...

Pivot Table Help #3
I have a lot of data that I am trying to analyze with a pivot table and am not sure how to go about it. Columns are (1) District (2) Store # (3) 2003 Score - these are #s or text ("incomplete") (4) 2004 Score - these are #s or text ("incomplete") For each district, I am trying to find out 3 things: (1) % of stores incomplete (2) Average score for 2003 & 2004 - I've got this one working properly (3) % change between 2003 & 2004 I can successfully analyze the data in a spreadsheet but there is too much to go through and thought a pivot table was the way to ...

Chart update automatically
Sometimes I start a data list to chart, create a chart then add data and the chart automatically updates as I add to the data list. The X axis grows with each entry. Other times, I do what I think is the same thing but the chart lays dormant. I actually have to manually update the series range. Nothing I do seems to make it "automatic". Is there a way to make any chart (new or existing) update automatically when dynamic data is involved? Hi, The chart will update automatically if, - you based the chart on a List (Table in xl2007) - created and used a dynamic named range - i...

Subtract colums in pivot table
I have a pivot table that has the following characteristics (Excel 2007): -rows (down the left) are values: "# Employees", "Total Pay" -Columns (across top) are Dates I want to calculate the difference between different date columns. Example: I have: Date 5/23/2010 5/16/2010 5/24/2009 Total # Emp 10 15 5 30 Pay 1000 15000 500 16500 I'd like: Date 5/23/2010 5/16/2010 5/24/2009 Total Vs. Last Wk % Change Vs. Last Yr % Change # Emp 10 15 5 30 -5 -33% 5 33% Pay 1000 5000 250 6250 -4000 -80% 750 15% Can anyone tell me how to cre...

exchange 2007 rollup update OWA problem
After installing rollup update KB930809 for Exchange 2007 I can't access OWA from IE6. When I type https://192.168.241.5/owa browser change adress to https://192.168.241.5/owa/auth/logon.aspx?url=https://192.168.241.5/owa&reas on=0 and show blank page. But when I access OWA from Firefox, I can see logon screen but without pictures. Renko Focusing on the patch side: Are you 100% sure that the patch installed with no errors? The application eventlog will have an MSIInstall event for start and end results of the installation of the rollup. Look at events around that time. If every...

How to substitute for a non-existing column in a joined table
Hi, Is there a simpler way than a UNION to return a default value of a joined table for which a corresponding row does not exist? The following example (not a working one, of course) illustrates what I'm after. I'd like to return 'N/A' as c2name if there is no matching row in t2 SELECT t1.c1 (CASE t2.t1pk WHEN NULL THEN 'N/A' ELSE t2.name END) AS c2name, FROM t1 LEFT JOIN t2 ON t2.t1pk = t1.pk Thanks. On 2010-04-21 21:05, bob wrote: > Is there a simpler way than a UNION to return a default value of a joined > table for which a ...

update worksheet in one file with worksheet in another file
I want to have one worksheet in one of my excel files (file 1) to reflect the contents of a worksheet in another file (file 2) every time it is opened (file 1), that is, everytime it is opened this one tab should reflect the last changes made on the tab in another file, including formating. I suspect this is possible but I don't have a clue how to do it. I have a few questions: Can this be done without VBA? (I'm not even a beginner with VBA!) I did search the web some and found the following that looks interesting: http://techrepublic.com.com/5208-6230-0.html?forumID=101&threadI...

80070490 Error: Windows update encountered an unknown error 03-04-10
3 security updates will not complete (now 4). Error Code 80070490 02-Mar-10 I am unable to do these three security updates: KB970238(release date 6/9/09), KB97451(release date 10/13/09), KB954155(release date 10/13/09), & KB975517(release date 10/13/09). The last succesful update was KB9937286 on 2/27/2010 I have a Compaq Presario PC: SR5214X. OS Name Microsoft® Windows Vistaâ„¢ Home Basic Version 6.0.6002 Service Pack 2 Build 6002 OS Manufacturer Microsoft Corporation System Manufacturer Compaq-Presario System Model GV441AA-ABA SR5214X System Type X86-based PC ...

Pull Data From Multiple Tables ????
Hi I will have 4 tables name "TblCostomers","TblVendors","TblAccounts", TblExpenses" Now i have a for name "FrmDrVouchers" that has a table "TblDrVouchers" in source. This form has two TxtBox Control name "TxtAccountNo" and "TxtAccountName" If User enters a Account No., It pulls the Account Name from Any One of these Table. I can do this if I have only one table. But tell me how can i do it while I have 4 tables for One Field of a table Thank you.. -- Message posted via AccessMonster.com http://...

Renaming table in a dB
Is there a short way in which i can modify all references to a table after i rename it? Or would i have to open every query and form and manually change the table references?Thanksramesh Access doesn't provide a way to do this.There are commercial products that do, e.g.: http://www.speedferret.com/-- Allen Browne - Microsoft MVP. Perth, Western AustraliaTips for Access users - http://allenbrowne.com/tips.htmlReply to group, rather than allenbrowne at mvps dot org."Ramesh" <ramesh2020@gmaildotcom> wrote in messagenews:uGgN$EuZHHA.4000@TK2MSFTNGP02.phx.gbl...> Is the...

Dummy series and data table
Hi, I have a chart that presents 2005, 2006, 2007 summary data as a column chart and then 2007 by month as a line. To show the yearly data I have a yearly category, after which I have individual months where the yearly data is zero - sort of like a dummy series - because I only have one value for them. The 2007 detailed data has zero in the yearly column but all the individual values in the monthly columns. It worked fine until I was asked to add a data table to the chart. Now, since it has 2007 twice - once as the summarized for the year and the other as all these individual months - some us...

Tying tables to forms
I have four connected tables that work well as table input but when I put them in a form some of the fields will not let me make entries. Does this happen because I am using the Id fields and subsequent data from the wrong tables? -- Taylor It sounds like you have created a non-updatable form. One cautionary note first: Don't tie your forms directly to the tables. Use queries instead. The queries will act as a stop light for which data is written and when. If more than one person tries to make a change to the same record at the same time, you will run into problems. From wha...

combo box list updating
Hi - I would like my combo box list to change base on the value of A1. That is, I have its input range being B1:F1 - "Year 1", " Year 2" etc. Example: -If cell A1 is the value "10" then the combo box default list item would be "Year 1" (which is cell "B1") -If cell A1 is the value "20" then the combo box default list item would be "Year 2" (which is cell "C1") ETC... Thanks for your help - Jim A ...

User Entity
Hi, I was wondering how others keep the User records in CRM updated with the Active Directory information like phone numbers, etc. Has anyone written a Windows Script to take this action or are you using other methods? Thank you ...

Sumif across a table
I am looking for a function that works using a =sumif function to add things that are not in a range that are next to each other as seen below the letters in () are the columns that the values are in... so I am looking for a sum in column A "X" of the total work out time if the appl column is "Y" total work Running (D) Walking (F) Elliptical (H) out time (C) Appl (D) Time (E) Appl (F) Time (G) Appl (H) Time (I) X Y 20 N 0 Y ...

Automatically updating cells on other worksheets when data typed into primary sheet
I'm trying to create a workbook that will allow me to enter data on the first sheet and it will then append the data to a second sheet by automatically starting a new line. Example: I enter "This is a test" into A1 on sheet 1 First available line on sheet2 automatically updates with "This is a test" When it comes to enter something new in A1 on sheet1, same function above goes into action, accept it should be on the next available line which will be A2 on sheet2, as A1 is already used by previous data entry. Can this be done? I would appreciate any help with this. ...

Major instal issue
I have Money 2002 installed, and have had for some time. About two weeks ago, I started getting the Windows Installer "preparing to install" message repeatedly when I open Money, or when the Money Viewer is invoked in IE. Both Money and the viewer work fine, but no matter if I cancel or ignore the message, it keeps coming up roughly once every two seconds. The real issue though is this: since this started happening, I've been unable to install or uninstall anything else that uses the Windows installer (including trying to uninstall Money to solve the issue), as it fails saying t...

Pivot table
Can I develop a formula that I can add to those which you pick from whe using the wizard ie sum, average, min, max etc Specifically, I want to add an IF statement to give me a 'flag' i which to summarize the data with elsewhere. The data behind the pivo changes (sales data) and I am trying to flag new customers that hav never worked with us before.....once they have traded with us then the dissappear as they are now an old customer To -- Message posted from http://www.ExcelForum.com Hi no you can't do this -- Regards Frank Kabel Frankfurt, Germany > Can I develop a for...

Aging report table for accounts receivable
Can somebody tell me what is the table for a/r aging..I want to make query/view in sql server 2005 Michael, You need to use RM20101 and RM10201 tables. You need to use date functions to get the aging for your view based on document date or due date field. If your aging is setup to be by Doc Date, Consider Doc Date and If it is due date, you should be taking it by Due Date. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "Michael@nyresume.com" wrote: > Can somebody tell me what is the table for a/r aging..I want to make > query/view in sql server 2005...

Migrating from Novell to Windows 2000 server causes issues with .PST on Win95/98
All: Hopefully someone has seen this before and can provide a clue. We are currently migrating all of our user home directories from Novell 5 servers to Windows 2000 servers. The majority of our clients are XP and Windows 2000 workstations using Outlook 2000. However, there are still a number of clients using Windows 95/98 and Outlook 98. After copying .PST files from the Novell home directory to the Windows 2000 home directory using a Windows XP workstation, users using the Windows 95/98 workstations get an 'access denied error' when attempting to open their .PST folder from the ...

pivot tables #3
I am trying to change the order in how the tables display. I don't want an accending or decending alpha order as it is set up now. I would like to pick and choose how I want them listed. How do I change the order? ...

pivot table, How to add 1 column
I just need to add one column only but pivot table create another one? I thinkc because I have a column with 2 parameters. I can turn on / off with the field drop down menu How can I get around with this problem? Thanks Daniel ...

Weird Vista screen issues
Hey, my screen is really weird. My screen is both used for my computer and for my Xbox 360. Now, today when I switched the screen cable from the Xbox to my PC my screen is really weird. Firstly, I cannot seem to choose 1680x1050 as a screen resolution, as whenever I choose it it says it has changed but there's still weird screen stuff with it. Icons and text on my computer are much more unsmooth and it's really starting to annoy my eyes. What's up with mah scr33nz? -- Tomatketchup Tomatketchup wrote: > Hey, my screen is really weird. My screen is both us...