 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2007Replacing data in Pivot Table causes Field name change
Good morning, I have a pivot table report that uses about 1200 rows of data on a neighboring page. Today I produced new monthly data and pasted it on top of the old data. When I do this the report's left most column field names all change from FieldName to FieldName2. When I look in the drop down box for that field it seems to have duplicated the fields when I pasted the new data. I'm able to work around it by changing the displayed lables from FieldName2 to FieldName"space""space". I can't change to just FieldName because that is already in use. My fear is that next month when I do this again it will again dup the field name and I'll have to put in "space""space""space" etc. Any idea why it does this? I've tried: * deleting the data then pasting the new data * deleting the rows then pasting the new data * leaving the column headings in place and pasting only data * pasting everything including column headings The new data is slightly larger than the old. I have 2 versions of the report. One pulls from a static page. For the new data after I paste I have to go into the wizard and go "back" so as to increase the range of the data. My other version of the report uses a dynamically calculated Named Range, so all I have to do is paste and I'm good. Thanks for any ideas. Russ Sounds like you still have the original 'FieldName' in the data that the report relates to - can you make sure it isn't included and only the data for the new month is? ie when you 'go back' to change the range the report points at make sure the range only includes the new and not the old? On Apr 5, 2:08 pm, xrbbaker <xrbba...@discussions.microsoft.com> wrote: > Good morning, > [quoted text clipped - 24 lines] > > Russ CloudDoctor, Can't do. My original data range is A1:L209. The new data is A1:L1339. The new data is larger than the previous and is totally new data so I can just tack in on the end. I'm stumped. > Sounds like you still have the original 'FieldName' in the data that > the report relates to - can you make sure it isn't included and only [quoted text clipped - 32 lines] > > > > Russ I see from contextures.com the below hint. I guess that is my problem. Excel simply adds the new data on top of the old data cache. Since the column headings are the same as the old column headings it appends a "2" to the end of it to differentiate. To me that makes the pivot table about worthless. Is my understanding correct here? Also, I tried the instructions for the manual clear of old items and it didn't work. - Russ Old Items Remain in Pivot Field Dropdowns The data in the pivot table source may change, and items that were previously in the source are no longer included. For example, some sales reps may leave the company, and the names of their replacements appear in the source table. Even after you refresh the pivot table, the names of the old sales reps will appear, along with the new names. In the list at right, Cartier has replace Gill, but Gill still appears in the list. Manually Clear Old Items To manually clear the old items from the list: Drag the pivot field out of the pivot table. On the Pivot toolbar, click the Refresh button Drag the pivot field back to the pivot table ****************** > Sounds like you still have the original 'FieldName' in the data that > the report relates to - can you make sure it isn't included and only [quoted text clipped - 32 lines] > > > > Russ Maybe you're getting some false groupings. Select one of Fields that ends with a 2 On the PivotTable toolbar, choose PivotTable>Grouping and Selection>Ungroup > I see from contextures.com the below hint. I guess that is my problem. > Excel simply adds the new data on top of the old data cache. Since the [quoted text clipped - 63 lines] >>> >>>Russ  SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html Hi Debra, That's not it. I now know what the issue is, but still not how to correct. Aditionally, I should have done a better job of more accurately describing the problem as Value duplication, not Field duplication... Originally this PT report Field had values of ValueAA, ValueBB, ValueCC. When she saw the report the user didn't like the value labels and asked if they could be changed. I found out that all one had to do was go into the PT Report and edit the value and PT changed the values to ValueXX, ValueYY, ValueZZ across the board. Cool! This month the lady who produces in Business Objects the data for me to use as input also modified the Field to use ValueXX, YY, ZZ. Thus the PT somehow sees these new values coming in as conflicts??? with the modified values that I overlayed and it screws things up. (Technical term.) Knowing this, it would seem plausible that all I would have to do is what I just tried: * remove the dup field column from the report * add the new data to the data page * hit Refresh (without the field on the report) * add the field back on to the report In this way I would think it would clear out the old "overlay" values that I had input before. It does not. It still keeps them internally somewhere and chokes on the new data. Maybe with my new more accurate description what I need to do to fix is more obvious??? He hopes.... Thanks > Maybe you're getting some false groupings. > Select one of Fields that ends with a 2 [quoted text clipped - 68 lines] > >>> > >>>Russ Hi Did you try Debra's code for removing old items? This invariably works for me when I have old items in the list. http://www.contextures.com/xlPivot04.html SignatureRegards Roger Govier > Hi Debra, > [quoted text clipped - 134 lines] >> >>> >> >>>Russ Roger, That did the trick. I was trying to avoid using code since I'm a contractor and will leave here some day leaving this to folks who won't attempt any code. However, since it seems this is more like a one time clean up it shouldn't be a problem. Thanks for the advise. Thank you Debra for the corrective code. Regards > Hi > [quoted text clipped - 140 lines] > >> >>> > >> >>>Russ On Apr 6, 7:26 am, xrbbaker <xrbba...@discussions.microsoft.com> wrote: > Roger, > [quoted text clipped - 163 lines] > > - Show quoted text - Hi, Using code to clear the unwanted items is not necessary if you are using Excel 2002 or later. Simply drag the offending field of the pivot table, click Refresh, and drag the field back on to the table. Cheers, Shane Devenshire Microsoft Excel MVP Hi Shane > Using code to clear the unwanted items is not necessary if you are > using Excel 2002 or later. Simply drag the offending field of the > pivot table, click Refresh, and drag the field back on to the table. I am aware of this, and that is what I try first. There are some occasions where this does not work, however, as the OP had already tried and noted it didn't work. The code solution always does achieve the desired outcome. SignatureRegards Roger Govier > On Apr 6, 7:26 am, xrbbaker <xrbba...@discussions.microsoft.com> > wrote: [quoted text clipped - 37 lines] > Shane Devenshire > Microsoft Excel MVP If you run the code for 2002: http://www.contextures.com/xlPivot04.html#Clear2002 it sets the MissingItemLimit property to zero, so the problem shouldn't recur. In Excel 2002 and 2003, that setting is only accessible via programming. > Roger, > [quoted text clipped - 151 lines] >>>>>>> >>>>>>>Russ SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.