How to create a calculated field that references a cell value in an excel pivot table

1
votes

I have my data that looks like the following:

Col1 | Value
A    | 1
B    | 1
A    | 3
C    | 7

and a pivot table that sums up the values for col1. In addition I have an exchange rete in a another cell (in another worksheet) and I would like to create a calculated field in my pivot table that multiplies the Value for the exchange rate in that cell. Is this possible? How? The reason for this is that the users might change the exchange rate in the cell but they would not know how to change it in the calculated field. I know that I can simply add a column in my source data with the calculated value, but I would like to do it as calculated pivot table field if this is at all possible.

2
excelpivot-tablecalculated-field

2 Answers

3
votes

You can achieve something similar to what you're after by 'slaving' a Table to a PivotTable, with a little VBA. See this answer I posted, that can be adapted to effectively achieve what you want.

Or alternately, you could multiply the individual totals in the source data by a named range called Exchange_Rate, and have a small bit of VBA that refreshes the Pivot whenever that Exchange_Rate input parameter is changed by users.

Here's how that looks:

First Exchange Rate

...and if I change the ExchangeRate variable in that orange input cell:

enter image description here

To do this, I have a small snippet of VBA in the relevant Sheet Module that simply monitors that input cell for any change, and then refreshes the PivotTable so that the Pivot reflects the updated values in the underlying source data:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ExchangeRate")) Is Nothing Then ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Or you could record a macro while setting up a calculated field at a specific exchange rate, then modify that code so that the rate part gets replaced to match any change to that orange cell: Calc Field

...and here's the event handler code that does this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("ExchangeRate")) Is Nothing Then _
    ActiveSheet.PivotTables("PivotTable1").CalculatedFields("Local Cost"). _
        StandardFormula = "='Value ($USD)'/ " & Range("ExchangeRate")
End Sub

Or if you have the ability to use PowerPivot, you could use Rob Collie's Disconnected Slicer trick.

-1
votes

I've found an answer more true to the original question, even though you will need some additional fields in the source data. I've simply added a field with the desired parameter as a constant (say, "Exchange=$F$4"), and another field called "counter", always =1. In the calculated field, instead of multiplying the value directly by the exchange rate, I multiply it by (exchange/counter). This way I can have my calculated field, the user can change the variable at will, the performance impact is null, and I do not need any VBA whatsoever.

BTW, I think your original question is OK, you do specify that you need it only in calculated field.

See this example