1

I have the following Excel spreadsheet:

enter image description here

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:

enter image description here


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

enter image description here

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.

Michi
  • 4,663
  • 6
  • 33
  • 83
  • Could you have F1 trigger a worksheet_change event that rewrites the calculated field formula? –  Jun 12 '18 at 10:09

1 Answers1

0

enter image description here

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.

enter image description here

  • Hi Jeeped, thanks a lot for your answer. It works if the PivotTable is inserted in the same sheet as the database. However, I would prefer to have the flexbility to insert the PivotTable in another sheet; therefore, I tried to change the part: target.address = "$F$1" in your code to: target.address = Sheet1.Range("$F$1") but it does not work. Where is my mistake? – Michi Jun 12 '18 at 11:18
  • You're trying to keep the worksheet_change on the same code sheet as the pivot and remotely identify F1 on another sheet. It should be the other way around. Leave the code on the F1 worksheet's code sheet and identify the pivot on another worksheet. e.g. `worksheets("sheet2").PivotTables(...` –  Jun 12 '18 at 11:23
  • Perfect. Thanks a lot for your answer. – Michi Jun 12 '18 at 11:36