Displaying Source of Pivot Table Above Pivot Table

[jchiapp]

Colleagues

I was wondering/hoping that there is a function that allows me to display the source of a pivot table above the pivot table?

I ask because I create a number of pivot tables and often cannot recall where they are sourcing to validate the data.  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.  Then if the table changes or I change the name of the table, then I have to repeat the process. 

Thanks for all your help!

[#2 · Accepted solution]

@jchiapp 

Activate the Visual Basic Editor (Alt+F11).

Select Insert > Module.

Copy the following code into the module:

Function PTSource(PTName As String) As String
    Dim pvt As PivotTable
    Set pvt = Application.Caller.Parent.PivotTables(PTName)
    PTSource = pvt.SourceData
End Function

Switch back to Excel.

Use the following formula in a cell near the pivot table:

=PTSource("MyPivot")

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.

[#3]

@Hans Vogelaar 

This awesome! I am going to have to try that.  Knowing how Microsoft works, hopefully they are watching these posts, and add this as an additional function.  

Thanks so much!