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
<[email protected]> 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
> <[email protected]> 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

This is the worst necro post I've ever seen I know. Sorry not sorry.

Google brought me here and the above function from debra does EXACTLY what I needed. well did do what I needed.

my objective is to quickly check if a refreshed pivot table is capturing all the new data that was put into it. I have 2 boxes with a 3rd that asks if they are equal and is conditionally formatted to give a color depending on the result.

1st box has the formula: =ROUND(LEFT(RIGHT(A1,8),5),0) with "A1" being the PTSOURCE function result debra mentions above (there are ~10k rows so I wanted the 5 digit row # to compare against the total non blank rows in the 2nd box)

2nd box has the formula: =(COUNTA(Surveillances!A:A)) which just counts how many cells in the first column have data.

a button fires the below macro that refreshes the data.

Please Login or Register  to view this content.

This worked fine until I realized that the refresh macro was stored in my personal workbook folder and not the file itself. I moved where it pulled from and ever since the function returns only a #value error. I typed it more than once perfectly, copied/pasted, etc. I replaced it into my personal folder as well as the workbook and it still won't work.

any ideas?

Thanks.