Dynamic Calculated Field in Pivot Table

[ojsevans]

Hello,

I'm currently working with a Pivot table similar to the attached.

I have added the column 'Sum of Target' by adding a calculated field (as this seemed the easiest way, if there are any other ways of inputting this information this might help), however currently the calculated field is a static value and so if I wanted to change the target value I would need to go into the calculated field settings. Ideally I would like this to reference cell E18 (the cell next to target) so it can be easily altered. However Excel doesn't seem to allow this.

Does anyone have any suggestions?

Any help would be appreciated.

Kind regards,

Oliver

[spitfireblue]

Hi Oliver,

You just need to add a column into your data that references the target field, as per the example below...

Example.xlsx

Stephen

[ojsevans]

Hi Stephen,

Thanks for your reply.

The only problem I'm having with this is that when I add additional sales for an existing sales person then it pulls the target across multiple times.

Example Pivot updated.png

[stasinek]

I suggest such a solution

[spitfireblue]

Fair call. stasinek's correction to the formula works.

The other way you could do it is by adding a macro that amends the calculated field when your target cell changes.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim KeyCells As Range, val As String
    Set KeyCells = Range("D16")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
    val = "=" & Range("D16").Value
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("Field1"). _
        StandardFormula = val
    End If
End Sub

Stephen

[ojsevans]

That worked really well.

Thanks both for your help.

One last question. Say if there was a salesman 6 who has made no sales this month. Is there also a way to have him show on the Pivot without having to input a sale of 0?

Kind regards,

Oliver

[spitfireblue]

Hi Oliver,

I don't think so. Maybe you could have a list of all of your salesmen at the top of your data showing sales of 0? Or a check to see if you need to add them in with 0 sales?

Stephen