Need Formula to display pivot table source data
 

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

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 wrote:
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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

It worked like a charm.

Thanks!!!
Don S


On Wed, 23 Feb 2005 12:20:38 -0500, Debra Dalgleish
wrote:

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 wrote:
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

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

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

It worked like a charm.

Thanks!!!
Don S


On Wed, 23 Feb 2005 12:20:38 -0500, Debra Dalgleish
wrote:


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 wrote:

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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html