Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have to compare export files with original data prior to loading into a new software system. So I have a sheet where I stack the export file data on top of the system data. In Column A for the export data has "Export" in each row, and for System Data it has "System". I then create a pivot table with Cost as the data, account as the row field and Column A as the column fields. They nicely line up so I can see if the totals per account agree System vs. Export. So far so good. I would like to make the table show any differences. My only idea centered aound a calculated field, but since System and Export are in the same column A, that won't work. So I wind up having to do formulas to the side, which then disappear or shuffle around as I change the way I'm looking at data. So my question is: Is there any other way to get that calcualtion inside the table. It would save me much work. Thanks in advance.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You need a calculated item, not a calculated field. Define one as System-Export in the columnA field.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't understand the situation See the upload attached. I thougth I was doing as suggested, but must be missing something important. I'm looking for the table to include a filed such as COL F shows, so I'm not constantly doing Column F each time teh table changes. Thanks again.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts
    I just had a try at adding a calculated item - I found that you have to select cell B4 on the pivot table to do what you want. By selecting this first and then using the Pivot Table options to add a calculated item, this will give you a calculated item after the Old column and before the Grand Total Column showing the difference between New and Old.

    Regards,
    Maria
    Maria
    Simmo7
    Victoria, Australia

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Worked fine. Thanks!

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Worked fine. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •