+ Reply to Thread
Results 1 to 6 of 6

Pivot Table and Calculated Field

  1. #1
    Registered User
    Join Date
    04-08-2016
    Location
    California
    MS-Off Ver
    windows 7
    Posts
    28

    Pivot Table and Calculated Field

    Hi,

    Every month I update a pivot table with the new monthly sales I import from a data base. I categorize the sales into four categories- actual, forecast, plan, and forecast prior. So these four categories are imported easily into a pivot table template after I hit refresh. Now, I create calculated fields to show the variances between forecast and actual, etc.

    So, when I hit refresh in pivot table all my data updates by the month except the calculated fields. They are still the same as last month's numbers. I know you can manually update the the formula in the calculated field. But that takes way too much time, because I need to find the field I want to change and I have so many fields that need to change.

    I am wondering if there is another way to for me to update the fields formulas,( instead of me going to the calculated fields tab, and finding the field and modifying it). Is there a more efficient method?

    Thanks

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Pivot Table and Calculated Field

    I do not understand why you must update calculated field formulas. Do field names change?
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  3. #3
    Registered User
    Join Date
    04-08-2016
    Location
    California
    MS-Off Ver
    windows 7
    Posts
    28

    Re: Pivot Table and Calculated Field

    Hi,

    Yes they change every month. For instance, in February the field names would
    include February sales actual, February sales forecast, etc. And then the calculated fields would
    be Feb sales actual-feb sales forecast. And then in March,I would refresh the pivot table( the date source by now has march data)and now
    I would add March sales actual, march sales forecast,etc, into the pivot table and delete February info from the pivot table.
    But the calculated fields( the variance) would not update using March's data, unless I manually change the formula from February to March.

    Does it make sense now? If not please let me know.

    Thanks

  4. #4
    Registered User
    Join Date
    04-08-2016
    Location
    California
    MS-Off Ver
    windows 7
    Posts
    28

    Re: Pivot Table and Calculated Field

    Hi,

    Yes they change every month. For instance, in February the field names would
    include February sales actual, February sales forecast, etc. And then the calculated fields would
    be Feb sales actual-feb sales forecast. And then in March,I would refresh the pivot table( the date source by now has march data)and now
    I would add March sales actual, march sales forecast,etc, into the pivot table and delete February info from the pivot table.
    But the calculated fields( the variance) would not update using March's data, unless I manually change the formula from February to March.

    Does it make sense now? If not please let me know.

    Thanks

  5. #5
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Pivot Table and Calculated Field

    I think it will be easier to use only Sales actual and Sales forecast for titles. If you do only have one month data, it will not be confusing and calculated field will not require to be changed.

  6. #6
    Registered User
    Join Date
    04-08-2016
    Location
    California
    MS-Off Ver
    windows 7
    Posts
    28

    Re: Pivot Table and Calculated Field

    That is true, thanks for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] A pivot table field calculated using other field values as fields?
    By chrisf78 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-07-2015, 03:08 AM
  2. [SOLVED] Referring to a Sub-Field on Calculated Field Pivot Table Column?
    By figo12 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-27-2014, 02:02 PM
  3. Calculated pivot table field using pivot table data in calculation
    By BrittleStar in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-10-2012, 05:35 PM
  4. Replies: 0
    Last Post: 06-26-2012, 09:06 PM
  5. Replies: 1
    Last Post: 04-12-2010, 01:01 PM
  6. Replies: 3
    Last Post: 06-06-2006, 12:10 AM
  7. Replies: 0
    Last Post: 01-25-2006, 04:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1