Formulas Outside of Pivot Tables

[Tluv]

I turned off GETPIVOTDATA because i am creating formulas and calculations outside of the pivot table, but referencing the pivot data.
In the formula external to the pivot table I am referencing the last two rows of the pivot table (the last row of data and the total row). But when the size of the pivot table changes, the formula cell references error, and i have to rebuild the formulas. Is there a way to keep my formula cell references relative to the last two rows of the pivot table? Thankyou.

 

[montecarlo2012]

Yes, you can use dynamic range references in your formula to ensure that your references always include the last two rows of the pivot table, regardless of its size. Here's how to do it:

1. Select the data area of your pivot table, including the headers.
2. Go to the Formulas tab in the ribbon, and click on the Define Name button in the Defined Names group.
3. In the New Name dialog box, enter a name for your dynamic range, such as "PivotData".
4. In the Refers to box, enter the following formula:

=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1, COUNTA(Sheet1!$1:$1))

Note: Replace "Sheet1" with the name of the worksheet where your pivot table is located.

1. Click OK to save the dynamic range.
2. In your external formula, use the dynamic range reference instead of the specific cell references. For example, if your dynamic range is named "PivotData", and you want to reference the last two rows of the pivot table, you could use the following formula:

=SUM(PivotData[#Data][Sales])

This formula will always reference the Sales column in the last two rows of the pivot table, regardless of its size.

By using dynamic range references, you can ensure that your external formulas always include the appropriate rows and columns from your pivot table, even if its size changes.

Sub CreateDynamicRange()
    
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim lastRow As Long
    
    'Set the worksheet and pivot table objects
    Set ws = Worksheets("Sheet1") 'Replace "Sheet1" with the name of your worksheet
    Set pt = ws.PivotTables("PivotTable1") 'Replace "PivotTable1" with the name of your pivot table
    
    'Find the last row of the pivot table
    lastRow = pt.TableRange1.Rows.Count
    
    'Create a dynamic range based on the last two rows of the pivot table
    ws.Names.Add Name:="PivotData", RefersTo:=pt.TableRange1.Resize(lastRow - 1, pt.TableRange1.Columns.Count)
    
    'Use the dynamic range in an external formula
    ws.Range("D2").Formula = "=SUM(PivotData[[Sales]:[Sales]])"
    
End Sub

 

[Tluv]

Yes, you can use dynamic range references in your formula to ensure that your references always include the last two rows of the pivot table, regardless of its size. Here's how to do it:

1. Select the data area of your pivot table, including the headers.
2. Go to the Formulas tab in the ribbon, and click on the Define Name button in the Defined Names group.
3. In the New Name dialog box, enter a name for your dynamic range, such as "PivotData".
4. In the Refers to box, enter the following formula:

=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1, COUNTA(Sheet1!$1:$1))

Note: Replace "Sheet1" with the name of the worksheet where your pivot table is located.

1. Click OK to save the dynamic range.
2. In your external formula, use the dynamic range reference instead of the specific cell references. For example, if your dynamic range is named "PivotData", and you want to reference the last two rows of the pivot table, you could use the following formula:

=SUM(PivotData[#Data][Sales])

This formula will always reference the Sales column in the last two rows of the pivot table, regardless of its size.

By using dynamic range references, you can ensure that your external formulas always include the appropriate rows and columns from your pivot table, even if its size changes.

Sub CreateDynamicRange()
   
    Dim pt As PivotTable
    Dim ws As Worksheet
    Dim lastRow As Long
   
    'Set the worksheet and pivot table objects
    Set ws = Worksheets("Sheet1") 'Replace "Sheet1" with the name of your worksheet
    Set pt = ws.PivotTables("PivotTable1") 'Replace "PivotTable1" with the name of your pivot table
   
    'Find the last row of the pivot table
    lastRow = pt.TableRange1.Rows.Count
   
    'Create a dynamic range based on the last two rows of the pivot table
    ws.Names.Add Name:="PivotData", RefersTo:=pt.TableRange1.Resize(lastRow - 1, pt.TableRange1.Columns.Count)
   
    'Use the dynamic range in an external formula
    ws.Range("D2").Formula = "=SUM(PivotData[[Sales]:[Sales]])"
   
End Sub

Thank you Montecarlo, I will try this and update, much appreciated.

 

[Tluv]

Hi Montecarlo, I was able to successfully create my Dynamic Range Reference, and it's called "ClosePivot". Rows 44 and 45 (Grand Total) are the last two rows of the pivot table. On Row 49 is the formula I am attempting to float dynamically based on the size of the pivot table. I am calculating the Percent difference between the last row of data (R44) and the grand Total (R45). =(b44-b45)/b45. Can you please advise how I would write that formula using the Dynamic Range Reference "Close Pivot"? Very much appreciated.

 

[montecarlo2012]

I am not sure but maybe something like this:
=(INDEX(ClosePivot,MATCH("Grand Total",ClosePivot[Category],0)-1,2)-INDEX(ClosePivot,MATCH("Grand Total",ClosePivot[Category],0),2))/INDEX(ClosePivot,MATCH("Grand Total",ClosePivot[Category],0),2)

 

[Tluv]

Thankyou very much , ill update asap... i appreciate your help

 

[montecarlo2012]

welcome

 

[Tluv]

Hi montecarlo, I am not doing something correct. It does not interpret that formula syntax as a "formula". Is there a way I can get a copy of the file to you, to insert a working copy of the formula, then I'll copy it? Thanks