Jump to content


Photo

Excell Pivot Table Source


  • Please log in to reply
4 replies to this topic

#1 JPL

JPL

    Apprentice

  • Quark
  • 108 posts

Posted 28 April 2017 - 10:35 AM

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?



#2 galdarian

galdarian

    Master

  • Atomican
  • 752 posts

Posted 02 May 2017 - 08:10 PM

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, 04 May 2017 - 04:44 PM.

  • JPL likes this

#3 JPL

JPL

    Apprentice

  • Quark
  • 108 posts

Posted 03 May 2017 - 11:22 AM

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?

 

#4 galdarian

galdarian

    Master

  • Atomican
  • 752 posts

Posted 04 May 2017 - 04:43 PM

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, 08 May 2017 - 01:46 PM.

  • JPL likes this

#5 JPL

JPL

    Apprentice

  • Quark
  • 108 posts

Posted 05 May 2017 - 03:52 PM

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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users