extracting field info from getpivotdata formula

[Guest]

Hi,

I've got a pivot with a possibility for various row/column fields. I want
to click on one of the data values in the data section and it will return me
the value of a row field, only if that particular row field is being used.

My thought would be to use that automatic GetPivotData formula because it
lists out all relevant fields. However, how can I extract what I want from
this formula or is there another easier way?

Example

Country Prov City Population Highest Temp
Canada BC Van 1,222,222 35
Canda BC Surrey 400,000 45

When I click on 45, I want to know what City it belongs to.

Is this possible?

Thanks,
Carmen

 

[kemal]

Hi Carmen

1.Do you mean you can not see it because "Surrey" is not on your
pivot ?
2.If "Surrey" is visible on your pivot then what are you going to do
with it?
ie. planing to use it in a formula or in a macro or write somewhere
else ?

rgds

 

[Debra Dalgleish]

You could use a Worksheet_SelectionChange event to show a message, e.g.:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pt As PivotTable
On Error Resume Next
Set pt = Target.PivotTable
If Not pt Is Nothing Then
If Target.PivotField.Orientation = xlDataField Then
MsgBox Target.Offset(0, -3).PivotItem
End If
End If
End Sub

 

[Guest]

I need to allow users to change the data in the pivot (ie from 45 to 66) by
clicking in the pivot table. If they double click they'll activate the show
details event which is not intended.

I was thinking of using beforedoubleclick event, somehow, identify which
city they want to change, get the requested change and then modify the source
data. The complication is that this code will need to be flexible enough
that user can play with the format of the pivot. (ie City will not always be
the third row header)...

Through getpivotdata, I see the city listed, so was wondering if I can
extract that info from there.

Carmen

 

[Debra Dalgleish]

The automatically generated GetPivotData lists the fields, but it might
be difficult to extract the city name from that.

You could use the BeforeDoubleClick event, and set Cancel to true, so
the drilldown sheet isn't created. For example:

'============
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim pt As PivotTable
Dim lQty As Long

On Error Resume Next
Set pt = Target.PivotTable
If Not pt Is Nothing Then
If Target.PivotField.Orientation = xlDataField Then
lQty = InputBox("Enter the new value", _
"New Value", Target.Value)
'code to change source data
MsgBox Target.Offset(0, -3).PivotItem _
& " was changed to " & lQty
Cancel = True
End If
End If
End Sub
'====================