pivot table - Non-VBA way to show Excel PivotTable Data Source as cell value - Stack Overflow

admin管理员组

文章数量:1201982

Is there a non-VBA way to show the PivotTable Data Source in a cell in Excel?

I know we can manually view the Data source by clicking on

PivotTable Tools Analyze > Change Data Source > Change Data source to display source

but this has to be done for each pivot table.

I have inherited lots of workbooks with lots of pivot tables across lots of tabs, referencing lots of tabs. I know some are referencing the wrong source data - e.g. a table labelled as 2023 is referencing 2022 data. Rather than clicking in each pivot table to see what its source data is, can I do something to show the source data next to the table, so I can just scan down and spot ones looking at the wrong place?

I am hoping there's something like:

=GETPIVOTDATA("Source",{cell ref somewhere in pivot table})

so I can leave the cell ref as dynamic and paste it in next to each pivot table.

(.png)

I tried searching Excel functions and couldn't come up with anything.

I tried googling the answer and have only found one option, and it uses VBA:

display Excel PivotTable Data Source as cellvalue

I can't use it as I'm not familiar enough with VBA to understand how to apply that to my spreadsheet. It also mentions that you need to reference the pivot table number - the ones in my spreadsheet weren't created in any "nice" order, so I'd still have to click in them all to see what their number was, defeating the point of avoiding clicking in each one and checking Change Data Source.

Thanks for any ideas.

Is there a non-VBA way to show the PivotTable Data Source in a cell in Excel?

I know we can manually view the Data source by clicking on

PivotTable Tools Analyze > Change Data Source > Change Data source to display source

but this has to be done for each pivot table.

I have inherited lots of workbooks with lots of pivot tables across lots of tabs, referencing lots of tabs. I know some are referencing the wrong source data - e.g. a table labelled as 2023 is referencing 2022 data. Rather than clicking in each pivot table to see what its source data is, can I do something to show the source data next to the table, so I can just scan down and spot ones looking at the wrong place?

I am hoping there's something like:

=GETPIVOTDATA("Source",{cell ref somewhere in pivot table})

so I can leave the cell ref as dynamic and paste it in next to each pivot table.

(https://i.sstatic.net/TMYPrD9J.png)

I tried searching Excel functions and couldn't come up with anything.

I tried googling the answer and have only found one option, and it uses VBA:

display Excel PivotTable Data Source as cellvalue

I can't use it as I'm not familiar enough with VBA to understand how to apply that to my spreadsheet. It also mentions that you need to reference the pivot table number - the ones in my spreadsheet weren't created in any "nice" order, so I'd still have to click in them all to see what their number was, defeating the point of avoiding clicking in each one and checking Change Data Source.

Thanks for any ideas.

• excel
• pivot-table

Share Improve this question Follow edited Jan 23 at 5:44 user1 2566 bronze badges asked Jan 22 at 11:58 LouiseLouise 1311 silver badge33 bronze badges 1

• There is no built-in formula for that. Amending that VBA function so that you can simply refer to a cell in the pivot table (as with GETPIVOTDATA) would be easy enough though. – Rory Commented Jan 22 at 12:07

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Example per my comment of how to use a cell reference rather than a pivot table name or index:

Function GetPivotTableSource(pcell As Range) As String
    Dim a1Source As String
    Dim bracket As Long
    Dim pt As PivotTable
    On Error Resume Next
    Set pt = pcell.Cells(1).PivotTable
    On Error GoTo 0
    If pt Is Nothing Then
        GetPivotTableSource = "Not a pivot table cell"
    Else
        a1Source = Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)
        bracket = InStr(1, a1Source, "]")
        GetPivotTableSource = "=" & Mid(a1Source, bracket + 1)
    End If
End Function

本文标签： pivot tableNonVBA way to show Excel PivotTable Data Source as cell valueStack Overflow