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:

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

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

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?

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.

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.