Copying pivot table formulas to another worksheet so the worksheet is dynamic and will upd

[ccbank]

Currently, there are over 700 rows on my pivot table and i need to copy the formula to another worksheet with a sample of the cell formula as such

"=GETPIVOTDATA("[Measures].[Average Book Balance]",'[Detailed Forecast Audit.xlsm]Deposit Forecast'!$A$11,"[Period].[FlatPeriodUS]","[Period].[FlatPeriodUS].&[10/31/2012]","[Account].[Account]","[Account].[Account].&[48]","[Liquidity Tag Custom].[Liquidity Tag Custom List]","[Liquidity Tag Custom].[Liquidity Tag Custom List].&[165]")"

The date and account tag will change accordingly as months changes and when the extra rows of data are inserted.

Is there a way for me to copy all 700 rows of formula to the second worksheet without have to type "= " and reference the original pivot table to get cell by cell formula?

[dilipandey]

HI ccbank,

I believe you can not use getpivotdata function on a different workbook to fetch data from a pivot table created on a different workbook. Alternatively, you can copy the formula and paste special as a link on different workbook Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[ccbank]

DiliPandey,

I did so currently cell by cell with the "=" then to the original pivot table data cell to get the info. That was why the work is tedious and time consuming and I thought there should be a quicker way. I tried clicking on the pivot table itself to copy the formula as you have suggested but I don't see the formula when I move the mouse over the pivot area. Let me know the mechanics of the step you suggested to copy over the pivot table formula.

Thanks

[dilipandey]

okay.. you have the getpivot formulas somewhere in the workbook having pivot .. correct?
Now copy these formulas and go to some other workbook's sheet and paste special -> link. That's it.

Regards,
DILIPandey

<click on below 'star' if this helps>

[ccbank]

No, You misunderstood. the getpivot is a result of time consuming action I am trying to avoid in the other workbook by referencing "=" and go back to the original pivot table cell by cell getting the getpivot formula. If I am able to see the pivot table and its "getpivot info" on the same workbook, then I would hae used your advice and copy\past special to another workbook

[MarvinP]

Hi ccbank,

You started by asking about seeing it in another worksheet and then the question went to another workbook. We really need a sample file to do much here.

[ccbank]

Another worksheet or another workbook- the same problem apllies. I need to copy over the formulas from the pivot table to another another worktab or workbook and the only way is to manually type "=" and then back too the pivot table cell by cell to get the formula.

eg: =GETPIVOTDATA("[Measures].[Average Book Balance]",'[Detailed Forecast Audit.xlsm]Cash Breakout'!$A$11,"[Period].[FlatPeriodUS]","[Period].[FlatPeriodUS].&[10/31/2012]","[Account].[Account]","[Account].[Account].&[-436]")

Unfortunately, I cannot upload a sample pivot due to confidential info

[MarvinP]

Just select the whole pivot table and copy it into the window clipboard. Go to the other worksheet or workbook and Paste Special -> Values Only.

Is that what you want? Mr. Excel is said to use this process a lot.

[ccbank]

It is not the values I need but the FORMULA within it so that my spreadsheet outside of the pivot table will change when the pivot table changes.

[dilipandey]

Hi ccbank,

I believe GETPIVOTDATA function is unable to fetch data if applied to a workbook which is not the same workbook having PIVOT table..

Okay...let me try explaining a little more here:-

You have workbook A having pivot table
You also have getpivotdata formulas in workbook A

Now, you can NOT have getpivotdata function in workbook B to extract data from the pivot table in workbook B ( and also if you copy them from workbook A, you'll get error)

BUT, as I suggested

You can copy all the getpivotdata formulas in workbook A,
Go to Workbook B
Do paste special - > Link

Hope this is clear. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[ccbank]

I haven't had a chance to refresh data after getting all the "Getpivotdata" cells . will let you know if it is indeed the case I will get that error or will the Workbook B data get refreshed as workbook A pivot table is refreshed.

[dilipandey]

Sure..

Regards,
DILIPandey

<click on below 'star' if this helps>