+ Reply to Thread
Results 1 to 5 of 5

Copy and paste formula outside pivot table using information from table

  1. #1
    Forum Contributor
    Join Date
    06-28-2009
    Location
    newcastle, uk
    MS-Off Ver
    Excel 2010
    Posts
    171

    Copy and paste formula outside pivot table using information from table

    Hi all,


    I have a pivot table with lots of information on it, and I am wanting to add a formula outside the pivot table that divides one cell by another from within the pivot. At the minute it will let me do the first one but then when I try and copy and paste the formula down, the same information from the original formula shows. Is there a way of copy and pasting the formula so that it changes information? I have attached the pivot table to show you what I am looking for. Eventually there will be many of these pivots so will be a long task manually setting up the formulas to divide the two cells.

    Thanks for looking,
    Gemma
    Attached Files Attached Files
    Last edited by batjl9; 07-01-2014 at 06:44 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Copy and paste formula outside pivot table using information from table

    use the averagefunction of the pivot table instead.

    Otherwise amend the table and make the data there.

    After that the data is also available in the pivot table.


    There is also a function =getpivottable (see for the right name of it in the help of excel).

    Another way of doing it is =c7/b7 and drag down.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Contributor
    Join Date
    06-28-2009
    Location
    newcastle, uk
    MS-Off Ver
    Excel 2010
    Posts
    171

    Re: Copy and paste formula outside pivot table using information from table

    Quote Originally Posted by oeldere View Post
    use the averagefunction of the pivot table instead.

    Otherwise amend the table and make the data there.

    After that the data is also available in the pivot table.


    There is also a function =getpivottable (see for the right name of it in the help of excel).

    Another way of doing it is =c7/b7 and drag down.
    Thanks for reply.

    The problem with adding to my pivot table through the source data is it would only give me the average per distance , and not for each individual race type per distance. The way I wanted to do it is as you mentioned, c7/b7 and drag it down, but when I do that it just keep the same data from the original formula, it doesn't manipulate the cells within the formula. I do remember something a few years ago where I needed to hold down buttons on the keyboard when entering an initial formula, but I can't remember what that was and whether it would even be the right thing to do for this problem.

    Thanks again
    Gemma

  4. #4
    Forum Contributor
    Join Date
    06-28-2009
    Location
    newcastle, uk
    MS-Off Ver
    Excel 2010
    Posts
    171

    Re: Copy and paste formula outside pivot table using information from table

    Just looked again and my problem is I have been doing the formula by clicking on c7 instead just typing it, so thank you it works fine. Obviously just a blonde moment this brunette was having lol.

    Thanks for your help,
    Gemma

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Copy and paste formula outside pivot table using information from table

    Your statement is correct, that in that case it is not reflect to the data in the pivot table (if you refresh them).

    So that could be a problem for the future.

    In excel 2007 there is also a calculated field for pivot table.

+ 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. 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
  2. Macro to copy Pivot Table information to another tab
    By Heinrich_83 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2012, 02:51 AM
  3. 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
  4. Copy table information and paste sorted based on specific field.
    By Paul in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-05-2005, 12:05 PM
  5. 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