Formulas based on Pivot Table
 

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?
--
Thank You,
Steve

Formulas based on Pivot Table
 

You could use the IF function with your current formula. For example:

=IF(F6="","",F6*12)

Steve D wrote:
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?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Formulas based on Pivot Table
 

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?

--
Thank You,
Steve


"Debra Dalgleish" wrote:

You could use the IF function with your current formula. For example:

=IF(F6="","",F6*12)

Steve D wrote:
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?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Formulas based on Pivot Table
 

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


Steve D wrote:
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?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html