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

Question

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.

Answer 1

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:

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

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:

...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.