• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Reference to newly created Pivot Tables every time in a normal table [SOLVED]

Asingham

New Member
Hi,


I used macros to create 3 different pivot tables in a one worksheet. I created a normal table which is a form of report whose calculations are based on the data from these 3 tables. Both these worksheets are in the same workbook.


I used GetPivotData to refer to these pivot tables in the calculations made in the normal table report.


Issue:

Before every time the macro creates these 3 pivot tables, it clears the worksheet. So, the normal table report is not automatically updating the reference to these pivot tables. The 3 pivot tables always have same range and they have pivot table names too.


Is there any way the table report form automatically picks up the newly created 3 pivot table references?


Kindly help! Thanks in advance:)
 
Would you mind posting the formula you are using? The GETPIVOTDATA function doesn't use the name of the PT in it's arguments, only the location. You shouldn't need to change anything.
 
When you say "Clears the worksheet", do you mean the worksheet where the pivot tables are, or the worksheet where the normal table is? Or are they one and the same?

How does it clear it? Does it delete it, and insert a new worksheet?
 
I used the location but not the name of the PT.


=GETPIVOTDATA("ABC",'Sheet1'!$A$3,"product",'Report'!C5)


A macro creates 3 pivot tables in the worksheet by name "Sheet1"

The report is in worksheet "Report"


Before the 3 pivot tables are created, the macro deletes the content in entire cells in the "Sheet1". So here is where the normal report table is loosing the reference to "Sheet1' $A$3.


=GETPIVOTDATA("ABC",'Sheet1'!#REF!,"product",'Report'!C5)


Solution: I created another macro that updates the text 'Sheet1' #REF! based on the column#. I've made sure there is no other such text down the cells in that column.
 
Sheets("Report").Select

Columns("D:D").Select

Selection.Replace What:="'Sheet1'!#REF!", Replacement:= _

"'Sheet1'!$A$3", LookAt:=xlPart, SearchOrder:=xlByColumns, _

MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
We might be able to "cheat" a little by using INDIRECT.

=GETPIVOTDATA("ABC",INDIRECT("'Sheet1'!$A$3"),"product",'Report'!C5)


The trick here is that the sheet reference is now a string, and doesn't care if the actual cell gets deleted, but we still get the benefit of referencing the same spot. The negative is that if there ever was a change you insert rows/columns that move the PT, the formula won't auto-adjust, but it doesn't sound like that is a problem.
 
Nice solution, Luke. I guess another solution is to change the macro so it clears the cells rather than flat out deletes them, meaning even the above genius approach wouldn't be needed. Particularly if this is a massive model, because the use of that volatile INDIRECT function might slow things down significantly.
 
Back
Top