Get data from an Excel Pivot Table depending on choices done from drop-down lists

Question

I have a workbook containing 2 sheets. The first one contains pivot table data.

The pivot table displays the cost from someone coming from a country and going to another country: for instance someone from France to Belgium = 80€. The 1st column contains the origin country and the 1st row: the destination country. The value is displayed in the cell of the intersection.

In my second sheet I have several row. For each row I have a form with 2 drop-down lists: The 1st (in the column "A") contains all values of the destination countries (1st column of the pivot table). The second drop-down list (in the column "B") contains all values of the origin countries (1st row of the pivot table).

I would like to show in a column ("C") for each row and after selecting the value for the both drown-lists (in the columns B and C) the value of the cost extracted from the pivot table.

I don't know exactly how to do that: perhaps with the function GETPIVOTDATA but I don't know how to search the value depending on the selected values?

I cannot use vba or macro for doing this task.

Could you please help me that ?

Regards,

Answer 1

Hopefully this gets you going in the right direction. Since the desired value is already in a cell, you can access it with intersection:

The left side of the pic shows the code & the result appears in the popup message box. The right is just an example of countries & the corresponding costs. Here's the code if you copy/paste & tweak it to your needs.

Private Sub whatever()
Worksheets("Sheet1").Activate
Set isect = Application.Intersect(Range("A2:C2"), Range("C1:C2"))
If isect Is Nothing Then
    MsgBox "Ranges do not intersect"
Else
    isect.Select
    MsgBox "The cost is: " & Selection.Cells.Value
End If
End Sub