[SOLVED] Non-VBA way to show Excel PivotTable Data Source as cell value

Non-VBA way to show Excel PivotTable Data Source as cell value

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.

Solution

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