Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
How to do two way lookup for more than 400 row & Coulmn? | Excel Worksheet Functions | |||
I need to create pivot table for the month but data in days | Excel Discussion (Misc queries) | |||
missing data on Pivot table | Excel Discussion (Misc queries) | |||
Formula to Extract Data from a Table | Excel Worksheet Functions |