relation between fields in PivotTable

  • Thread starter Thread starter Vlado Sveda
  • Start date Start date
V

Vlado Sveda

Hello everybody !

I have a question - how can I get "related value" from pivot table ?
(e.g. Customer name in column / pivotfield "Customer" for Work_ID in
column / pivotfield "Work_ID"

For Each pvtItm In
Sheets("PT_WorkConsumption").PivotTables("Works").PivotFields("Work_ID").PivotItems

' get value in related column "Customer"

Next pvtItm

Thanks in advance to all !
 
AFAIK you can't do this. A pivot is justy a view of a data from a database
requested using some SQL. You need to access that data directly.
Alternatively, if you have the items in columns in the PT, then try the
VLOOKUP() function.
 
hmmmm, pity ...
Nevertheless Thank you !
Vlado

Patrick Molloy said:
AFAIK you can't do this. A pivot is justy a view of a data from a database
requested using some SQL. You need to access that data directly.
Alternatively, if you have the items in columns in the PT, then try the
VLOOKUP() function.
Click to expand...
 
I'm pretty sure that is possible. I don't have an exact code snippet
on hand for that but here is something that might give you an idea how
to do it.

Sub ColorContractors(Optional Acolor As Integer, _
Optional Scolor As Integer, _
Optional Hcolor As Integer, _
Optional OthersColor As Integer)

Dim pt As PivotTable
Dim pf As PivotField
Dim pfit As PivotItem

Set pt = getPivotTable

For Each pf In pt.VisibleFields
If pf.Name = "Contractor" Then
Debug.Print "found " & pf.PivotItems.Count & " pivot
items"
For Each pfit In pf.PivotItems
Select Case pfit.Name
Case "A_Company"
If Not PivotItemSelect(pf, pfit) Is Nothing Then
If Acolor <> 0 Then
Selection.Interior.ColorIndex = BHIcolor
Else
Selection.Interior.ColorIndex = 36
End If
End If
Case "B_Company"
If Not PivotItemSelect(pf, pfit) Is Nothing Then
If Scolor <> 0 Then
Selection.Interior.ColorIndex = Scolor
Else
Selection.Interior.ColorIndex = 37
End If
End If
Case "Others"
If Not PivotItemSelect(pf, pfit) Is Nothing Then
If OthersColor <> 0 Then
Selection.Interior.ColorIndex =
OthersColor
Else
Selection.Interior.ColorIndex = 5
End If
End If
Case Else
If Not PivotItemSelect(pf, pfit) Is Nothing Then
MsgBox "unknown contractor: " & _
pfit.Caption & "/" & pfit.Name & "/" &
pfit.SourceName & _
" will not be custom colored!"
End If
End Select
Next pfit
End If
Next pf
End Sub

Function PivotItemSelect(pf As PivotField, pfit As PivotItem) As Range
Err.Clear
On Error Resume Next
pfit.Parent.Parent.PivotSelect pf.Name & "[" & pfit.Name & "]",
xlDataAndLabel, True
If Err.Number <> 0 Then
Set PivotItemSelect = Nothing
Else
Set PivotItemSelect = Selection
End If
Err.Clear
On Error GoTo 0
End Function
 
Thanks a lot - it gave me an idea, I will "warp" your code and use you idea
for me.
Once more time THANKS A LOT !!!

Vlado
 
Back
Top