Apply a fixed value dynamically to a calculated field in a pivot table

Question

I have the following Excel spreadsheet:

In Column C you can see the sales from the products in Column B. In Column A you can find the corresponding brand to each of the products in Column B.

Based on these datas I created the following PivotTable:

In my PivotTable I created a calculated field called sales per day with the following formula:

This gives me exaclty the result I need but as you can see I entered the number of days (in this case 360) as a fixed number into the function of the calculated field.

However, I would prefer to not enter this number as a fixed rate and have it flexible in my PivotTable so the user changes the number in Cell F1 in the database and it automatically applies correctly to the PivotTable.

Do you have any idea how I can solve this issue?
Is there a helper column I could use for it?

Sorry for only having the PivotTable descriptions available in German.

Answer 1

Put this code into the worksheet's private code sheet (right-click, View Code) not a public module code sheet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub

    If Target.Address = "$F$1" And CBool(Len(Target.Value2)) Then
        On Error GoTo safe_exit
        Application.EnableEvents = False
        If IsNumeric(Target.Value2) Then
Debug.Print Target.Address
            Me.PivotTables("PivotTable1"). _
               CalculatedFields("sales per day").StandardFormula = _
               "=sales/" & Target.Value2
        End If
    End If

safe_exit:
    Application.EnableEvents = True

End Sub

You may have to adjust some identifying names. Now, whenever you type a new numeric value into F1, the pivot table's calculated 'sales per day' field will have a new formula.