Formulas based on Pivot Table

  • Thread starter Thread starter Steve D
  • Start date Start date
S

Steve D

I believe this is possible but I can't remember how I did it in the past...

I have a pivot table that has formulas to the right and depending on the
filter I use I can have anywhere from 5 rows of data to 30,000 rows. How can
I have the formulas only appear where there is data? So if I have 5 records I
would have 5 rows of formulas and if I have 30,000 records I would have
30,000 rows of formulas.

Is this possible?
 
You could use the IF function with your current formula. For example:

=IF(F6="","",F6*12)
 
Thank You, but that does not solve my problem. Actually 2 problems, the first
being that I still will have all of the formulas in the file, causing the
file size to be unnecessarily large and the other issue being if my data
changes I do not know how many rows of formulas I actually need.

Any other thoughts?
 
To add the formula to only the rows adjacent to the pivot table, you
could use event programming. For example, on the code sheet for the
worksheet that contains the pivot table:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim lRow As Long
Dim pt As PivotTable
Set pt = Target

With pt.TableRange2
lRow = .Rows(.Rows.Count).Row
End With

With ActiveSheet
.Columns(6).ClearContents
.Range(.Cells(8, 6), .Cells(lRow, 6)).FormulaR1C1 _
= "=RC[-2]*0.05+10"
End With

End Sub
 
Back
Top