I am working with a Pivot Table that contains a calculated field which is simply revenue multiplied by a percent. The formula is hard coded within the Pivot Table, but users would like to be able to manipulate the percent used within the formula without having to manually change the formula itself (i.e. they would like to be able to enter a percent into a cell that exists above the pivot table and the formula within the pivot table will then recalculate using the user defined percent).
I have been told that I need to use a macro to acheive this as a pivot table calculated field can not refer to a cell.
I have messed around and created the following VBA code thus far, but when I enter a number into my cell I get an error saying that "The Value You Entered Is Not Valid; A user has restricted the values that can be entered into this cell." The below code is supposed to change the formula "EOTC Credit" that is found in the Pivot Table within the worksheet "Active Pivot" so that the percent entered into cell F8 on the "Active Pivot" worksheet is entered into the "EOTC Credit" formula. Cell F8 is a defined range titled var_EOTCCredit. The original "EOCT Credit" formula is (='SumOfFinal Imputed List Revenue' *0.01), where "SumOfFinal Imputed List Revenue is a field within the data that the pivot is built off of and "0.01" is the percent that was manually placed into the formula to start with. It is this percent that I would like to replace based on what the user defines.
Not sure if the code below can be fixed or just scrapped.....please help!!!!
_________________________________________
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Address = Target.Address Then
ActiveSheet.PivotTables(1).CalculatedFields("EOTC Credit").StandardFormula = "='SumOfFinal Imputed List Revenue' *" & ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Value & ")"
End If
End Sub
_________________________________________________________________________
Any help is VERY MUCH APPRECIATED!!!!
Thank you,
G
I have been told that I need to use a macro to acheive this as a pivot table calculated field can not refer to a cell.
I have messed around and created the following VBA code thus far, but when I enter a number into my cell I get an error saying that "The Value You Entered Is Not Valid; A user has restricted the values that can be entered into this cell." The below code is supposed to change the formula "EOTC Credit" that is found in the Pivot Table within the worksheet "Active Pivot" so that the percent entered into cell F8 on the "Active Pivot" worksheet is entered into the "EOTC Credit" formula. Cell F8 is a defined range titled var_EOTCCredit. The original "EOCT Credit" formula is (='SumOfFinal Imputed List Revenue' *0.01), where "SumOfFinal Imputed List Revenue is a field within the data that the pivot is built off of and "0.01" is the percent that was manually placed into the formula to start with. It is this percent that I would like to replace based on what the user defines.
Not sure if the code below can be fixed or just scrapped.....please help!!!!
_________________________________________
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Address = Target.Address Then
ActiveSheet.PivotTables(1).CalculatedFields("EOTC Credit").StandardFormula = "='SumOfFinal Imputed List Revenue' *" & ActiveWorkbook.Names("var_EOTCCredit").RefersToRange.Value & ")"
End If
End Sub
_________________________________________________________________________
Any help is VERY MUCH APPRECIATED!!!!
Thank you,
G