Jump to content
Forum upgrade is live! Read more... ×
JPL

Excell Pivot Table Source

Recommended Posts

Hello, quick question regarding Excell pivot tables and source data. I have a pivot table on one spreadsheet and multiple spreadsheets containing the source date.

I have used the following =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) which gives me the tab name of the spreadsheet the pivot table is on.

But is there a way that in another cell it's able to show the name of the spreadsheet the source data is on; and change as I switch between source data spreadsheets?

Share this post


Link to post
Share on other sites

If you're asking is there a formula that can return this information then the simple answer is 'not that I know of'.

 

However with a little VBA it is possible to get at the data you are after. Specifically the property you want is called pivotcache.sourcedata

I played around with it a little this afternoon and came up with the following code. If you want to give it a try open up VBA (alt + f11). Insert a new module (You may have to change the name to "Functions"), then copy and paste the code into the new module. You should then be able to use the function strPivotLocation like any other built in Excel formula. The function assumes the range you are entering is the top left of the PivotTable.

 

Lemme, know if you have any trouble.

Public Function strPivotLocation(rngRange As Range) As String
    Dim pvtT As PivotTable
    
    For Each pvtT In ThisWorkbook.Sheets(rngRange.Parent.Name).PivotTables
        If (pvtT.TableRange1.Row = rngRange.Row) And (pvtT.TableRange1.Column = rngRange.Column) Then
            strPivotLocation = rngR1C1toA1(pvtT.PivotCache.SourceData).Address(external:=True)
        End If
    Next pvtT
End Function

Public Function rngR1C1toA1(strR1C1 As String) As Range
    Dim intRow As Long, intColumn As Long
    
    intColumn = InStr(1, strR1C1, "!") - 1

    If intColumn > 0 Then
        Set rngR1C1toA1 = Worksheets(Left(strR1C1, intColumn)).Range(rngR1C1toA1(Mid(strR1C1, 2 + intColumn)).Address)
    Else
        intColumn = InStr(1, strR1C1, ":") - 1
        If intColumn > 0 Then
            Set rngR1C1toA1 = Range(rngR1C1toA1(Left(strR1C1, intColumn)), rngR1C1toA1(Mid(strR1C1, 2 + intColumn)))
        Else
            intColumn = InStr(1, strR1C1, "C")  'Return an index which can be used in the next 2 calculations
            intRow = CLng(Mid(strR1C1, 2, Len(strR1C1) - intColumn))
            intColumn = CLng(Mid(strR1C1, intColumn + 1))
            Set rngR1C1toA1 = Cells(intRow, intColumn)
        End If
    End If
End Function

Edited by galdarian
  • Like 1

Share this post


Link to post
Share on other sites

Thanks galdarian that seems to have worked but it then brings up the name of the work book, the sheet name and the cell range of source data. Can it be modified so that it only brings up the sheet name?

Share this post


Link to post
Share on other sites

Hmmmm, Yeah sure.

 

The quick and dirty way to do that is to wrap a mid string around it like you did above

 

I.e. Return the

=strPivotLocation(Sheet2!$A$3)

And assuming that formula is in cell A1 put this formula in another cell.

=MID(A1,FIND("]",A1)+1,FIND("!",A1)-FIND("]",A1)-1)

Alternatively, if you wanted to do all that in VBA then you can delete everything I posted before and replace it with just the following

Public Function strPivotLocation(rngRange As Range) As String
    Dim strTemp As String, intI As Integer, pvtT As PivotTable
    
    For Each pvtT In ThisWorkbook.Sheets(rngRange.Parent.Name).PivotTables
        If (pvtT.TableRange1.Row = rngRange.Row) And (pvtT.TableRange1.Column = rngRange.Column) Then
            strTemp = pvtT.PivotCache.SourceData
            intI = InStr(1, strTemp, "]") + 1
            strPivotLocation = Mid(strTemp, intI, InStr(1, strTemp, "!") - intI)
        End If
    Next pvtT
End Function


Comparing that to the original solution this is actually significantly simpler as you don't need to convert it to a proper range first and can just work with the text that is returned by PivotCache.SourceData.

 

edit: Formatting and Clarity

Edited by galdarian
  • Like 1

Share this post


Link to post
Share on other sites

Thanks galdarian; that worked a treat. Thanks again for your help.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×