Need Formula to display pivot table source data

[Don S]

I have a workbook with multiple worksheets of data from which I create
pivot tables. I need a way to display in a cell above my pivot table
the range name (I use dynamic range names for the source data) of the
source data used. In other words, I need to know which data table the
pivot table used without having to select "Data-Pivot Table-Back" each
time. There are about 50 pivot tables in this workbook and all have
to be updated each month.

Thanks!
Don S

 

[Debra Dalgleish]

You could create a user defined function:

In a regular module in the workbook, enter the following code:

Function PTSource(rng) As String
Dim pt As PivotTable
Set pt = ActiveSheet.Range(rng.Address).PivotTable
PTSource = pt.SourceData
End Function

Then, on the worksheet, type: =PTSource(A3)
Replace the A3 with a reference to a pivot table cell on your worksheet.
Note: this formula won't work for pivot tables created from multiple
consolidation ranges.

 

[Don S]

Bless you Debra. I've been trying to do that off and on for months.

It worked like a charm.

Thanks!!!
Don S

 

[Debra Dalgleish]

You're welcome. Thanks for letting me know that it worked.