Displaying Source of Pivot Table Above Pivot Table

Question

ColleaguesI was wondering/hoping that there is a function that allows me to display the source of a pivot table above the pivot table?&nbsp;I ask because I create a number of pivot tables and often cannot recall where they are sourcing to validate the data.&nbsp; To find the source, I have to manually go into PivotTableAnalyze, click on change data source, copy, and paste to a cell near the pivot table.&nbsp; Then if the table changes or I change the name of the table, then I have to repeat the process.&nbsp;Thanks for all your help!

hansvogelaar · Accepted Answer

jchiapp&nbsp;
Activate the Visual Basic Editor (Alt+F11).
Select Insert &gt; Module.
Copy the following code into the module:
&nbsp;
Function PTSource(PTName As String) As String
    Dim pvt As PivotTable
    Set pvt = Application.Caller.Parent.PivotTables(PTName)
    PTSource = pvt.SourceData
End Function
&nbsp;
Switch back to Excel.
Use the following formula in a cell near the pivot table:
&nbsp;
=PTSource("MyPivot")
&nbsp;
where MyPivot is the name of the pivot table (you can see this name when you click in the pivot table and activate the Analyze tab of the ribbon.
You will still have to edit the name in the formula if you rename the pivot table.
Don;t forget to save the workbook as a macro-enabled workbook (.xlsm) and to allow macros when you open it.

Forum Discussion

Displaying Source of Pivot Table Above Pivot Table

2 Replies

Resources