+ Reply to Thread
Results 1 to 9 of 9

Copy from pivot table and paste with formula

  1. #1
    Forum Contributor
    Join Date
    06-27-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    183

    Copy from pivot table and paste with formula

    Hi Friends,

    Is there any way to copy pivot table and paste with formula? i mean like pasting pivot table with value, i need to paste with formula such as SUM, AVERAGE etc..

    Kindly help.

    Prabhu
    Regards,

    Prabhu

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Copy from pivot table and paste with formula

    Have you tried the GetPivotData function - this does exactly what you want


    To see how it works do this:
    - find an empty cell next to your pivot table
    - look at your Pivot Table for a cell that contains a formula (for example cell C10)
    - in your empty cell enter the following formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    - now look at what Excel has changed that formula to - it's a bit of Excel magic!

    and this Microsoft page Convert Pivot Table Cells to Worksheet Formulas may also help
    Last edited by kev_; 03-04-2017 at 07:48 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    06-27-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    183

    Re: Copy from pivot table and paste with formula

    Hi Kev,

    Yes, but get Pivot is not working like formulas, i mean if i change any of the data value Grand total is not changing.

    I am looking for something like copy the pivot table and work like normal data and grand total field should have Sum, Average or count etc.. formulas. This will help even if i change any data value total automatically change according to formula.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Copy from pivot table and paste with formula

    What you are asking for sounds as though it would need rather complicated VBA, but there may be a different way to give you the results you need.

    Can you explain exactly what you are trying to achieve?
    - why are you wanting to change the values?
    - are you trying some "what ifs"
    - are there specific items that are forced into the pivot table that you do not want?
    - etc

  5. #5
    Forum Contributor
    Join Date
    06-27-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    183

    Re: Copy from pivot table and paste with formula

    Thanks Kev,

    I have attached a sample excel with data, pivot result and expected output with formulas.

    If this can be done without pivot thru VBA kindly suggest.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Copy from pivot table and paste with formula

    Thanks for the file - this gives the expected end result - but not what you are trying to do.

    We cannot go straight from A to B

    - Excel does not hold the formulas anywhere
    - sub-totals in pivot tables are simply a summation of a filter of the original data (eg columnA = central AND columnB = Jardine AND columnC = Pencil)
    - grand totals are simply a summation of all of those filters

    I may be able to give you a table that looks exactly like the one you want
    - but I need to understand what you are trying to do

    The pivot table already gives you all the correct totals etc, so there must be a reason why you want to have formulas included.

    Q1 Are you wanting to take the existing results and amend them in some way?
    or
    Q2 Are you wanting to create an empty table with all the formula? (to use perhaps for estimates for the future etc)
    or
    Q3 Is there a different reason?

  7. #7
    Forum Contributor
    Join Date
    06-27-2013
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    183

    Re: Copy from pivot table and paste with formula

    Yes, I want to take the existing results and amend them in some way.formula in the cells will give me instant result in all sub total and Grandtotal.As this amendments will happen every time so I am looking for formulas to be in the subtotal and grand total

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Copy from pivot table and paste with formula

    Ok - I am busy until tomorrow.
    But I think I can give you a way to achieve what you want, using standard Pivot Table features , instead of creating formulas.

  9. #9
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Copy from pivot table and paste with formula

    Unfortunately what I tried became too complicated.
    My idea was to
    - copy the pivot table values into a new working area
    - allow values to be amended there
    - use the amended values to generate a summarised data table
    - create a pivot table from the summarised table

    I made some progress, but not enough - I may have another attempt in a few days - I think I need to attack it from a different angle.

+ 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. Copy from pivot table and paste with formula
    By prabhubox@yahoo.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2017, 02:28 AM
  2. Copy/Paste cells from pivot table into another workbook
    By draser in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2015, 11:16 AM
  3. [SOLVED] Copy and paste formula outside pivot table using information from table
    By batjl9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-01-2014, 07:10 AM
  4. Macro to Copy Pivot Table and Paste a Regular Data Table in the Same Position
    By webfeet2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-01-2013, 06:34 AM
  5. copy paste only value from pivot table
    By barkarlo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2009, 05:05 PM
  6. Copy / Paste Pivot Table to PPT
    By skizz135 in forum Excel General
    Replies: 1
    Last Post: 07-08-2008, 02:36 PM
  7. Pivot table copy and paste
    By yllee70 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-23-2005, 04:06 AM

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