CODEWITHSUNDEEP
excelvbapivot-table
dieter declerck
dieter declerck

Reputation: 35

How to check if data from a pivot table is used in a GETPIVOTDATA formula

I use the data from the pivot table in formulas. There is a lot of data in the pivot table and I need to check, which data is already used in formulas.

I used already this piece of code to mark the used pivot data, but it does not work for GETPIVOTDATA formulas:

Cells(24, "g").Precedents.Interior.ColorIndex = 5

enter image description here

Upvotes: 1

Views: 987

Answers (1)

Asger
Asger

Reputation: 3877

As you already found out, the Range.Precedents of a cell with a GETPIVOTDATA formula unfortunately only points to the first cell of the pivot table. So you can not use precedents to identify an already referenced cell of a pivot table.

My code does following:

  • build the =GETPIVOTDATA() formula string for each pivottable's datarange cell
  • collect those possible formulas in an array
  • compare each used formula, e. g. within range G1:G500, with this array
  • if the formula is found, the corresponding pivotcell is marked yellow
    Private Sub EveryPossibleGETPIVOTDATAformula()
        Dim pt As PivotTable
        Dim pi As PivotItem
        Dim pc As PivotCell
        Dim c As Range
        Dim i As Long, j As Long
        Dim PossibleFormulas() As String

        Set pt = ActiveSheet.PivotTables(1)

        ReDim PossibleFormulas( _
            1 To _
            pt.PivotRowAxis.PivotLines.Count * _
            pt.PivotColumnAxis.PivotLines.Count)

        i = 1
        For Each c In pt.DataBodyRange.Cells
            Set pc = c.PivotCell
            PossibleFormulas(i) = _
                "=GETPIVOTDATA(""" & _
                pc.DataField.SourceName & """," & _
                pt.TableRange1.Cells(1).Address
            For Each pi In pc.RowItems
                PossibleFormulas(i) = PossibleFormulas(i) & _
                    ",""" & pi.Parent.Name & _
                    """,""" & pi.Value & """"
            Next pi
            For Each pi In pc.ColumnItems
                PossibleFormulas(i) = PossibleFormulas(i) & _
                    ",""" & pi.Parent.Name & _
                    """,""" & pi.Value & """"
            Next pi
            PossibleFormulas(i) = PossibleFormulas(i) & ")"
            i = i + 1
        Next c


        Dim myPosition As Variant
        For Each c In ActiveSheet.Range("G1:G500") ' adapt it
            If c.HasFormula Then
                myPosition = Application.Match(c.Formula, PossibleFormulas(), 0)
                If IsNumeric(myPosition) Then
                    pt.DataBodyRange.Cells(myPosition).Interior.Color = vbYellow
                End If
            End If
        Next c
    End Sub

Be aware: It works only, if =GETPIVOTDATA(...) is the only formula part in your cells. If you need further calculations, like a multiplication (you did it with =GETPIVOTDAT(...)*2), please try to separate that multiplication to an additional column.

Upvotes: 1

Related Questions