PDA

View Full Version : [SOLVED] update pivot dynamically based on range in separate worksheet



Nmarkit
08-29-2013, 08:42 AM
Hello

I have a pivot table with client name, volume, product and dates.

On a separate worksheet i have a list of products defined in a range - "product". this list changes depending on the selection made by the user from a drop down list. what i need is that depending on the values within this range the pivot table filters the products. Can someone please provide the vba code to do this? attached is a small data sample

Thanks!

p45cal
08-30-2013, 01:18 AM
For other's information:
cross posts
http://www.mrexcel.com/forum/excel-questions/723348-update-pivot-dynamically-based-range-separate-worksheet.html
http://www.excelforum.com/excel-charting-and-pivots/951185-need-to-filter-pivot-on-multiple-items-defined-in-range-on-separate-worksheet.html

Nmarkit, some light reading:
http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters
http://www.excelfox.com/forum/f25/message-to-cross-posters-1172/

I can't see a drop down list anyhere in your workbook apart from on the pivot table itself - is this what you mean?
Do you want to create a dropdown list? if so would a slicer do?
If so, when the pivot table is selected, the ribbon includes a Pivot Table Tools section, therein you'll see a Sort & Filter section and there you'll see Insert Slicer, click that, click the TYPE checkbox, OK, then you can put the slicer wherever you like, even on another sheet, you can also adjust how it looks etc. Is that what you're after?

Nmarkit
08-30-2013, 01:26 AM
The file attached is a sample one. I didn;t create a drop down in it. I put the output of the drop down selection on Sheet3 as I would need the pivot to be filtered on this output. Here's the code that is getting the selection from the listbox and dumping it on the separate worksheet. output is the name of the worksheet that the products are being dumped in.


Private Sub ListBox1_LostFocus()
Dim listItems As String, i As Long
With ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then listItems = listItems & .List(i) & ", "
Next i
End With

If Len(listItems) > 0 Then
Sheets("output").Range("J1") = Left(listItems, Len(listItems) - 2)
Else
Sheets("output").Range("J1") = ""
End If
Range("BE2:BE100").ClearContents
SplitAll
End Sub

Sub SplitAll()
Dim src As Range
Dim result As Variant
For Each src In Sheets("output").Range("J:J").SpecialCells(xlCellTypeConstants)
result = Split(src, ",")

'last cell in column C
With Cells(1, 57).End(xlUp)
Range(.Offset(1, 0), .Offset(1 + UBound(result, 1), 0)) = Application.WorksheetFunction.Transpose(result)
End With
Next src
End Sub

thanks for the reading material. will go through it now.

Nmarkit
08-30-2013, 01:38 AM
Apologies, my first time on forums so wasn;t aware on rules regarding cross posting. I have add the cross post details to the mrexcel forum. Not able to access excelforum for some reason but will update my post there as well once I;m able to get in. Thanks for your help with my query.

Nmarkit
08-30-2013, 03:11 AM
does the above code make sense? do you need any more details to figure this out?

p45cal
08-30-2013, 03:18 AM
Try this macro in the workbook you provided, it uses the list on sheet3 to set what's showing in the pivot table in Sheet1.
Sub blah()
Set PT = Sheets("Sheet1").PivotTables("PivotTable3")
Set ccc = PT.PivotFields("TYPE").PivotItems
PT.ManualUpdate = True
ccc(ccc.Count).Visible = True 'at least one must be visible at all times, so choose the last one.
Set TypeToShow = Sheets("Sheet3").Range("A1") 'would this be BE2 in your workbook?
Set TypeToShowStart = TypeToShow
Do Until IsEmpty(TypeToShow.Offset(1))
Set TypeToShow = TypeToShow.Offset(1)
Loop
Set showlist = Range(TypeToShowStart, TypeToShow)
For Each itm In ccc
itm.Visible = Not IsError(Application.Match(itm.Name, showlist, 0))
Next itm
PT.ManualUpdate = False
End Sub
if the list is blank it will fall over.

Nmarkit
08-30-2013, 03:27 AM
thanks works perfectly to update my pivot! thanks a lot :)

just one small issue though. if I select client A and then select product 1 and product 2 from the list then i get the updated graph. However if i now sleect client B from the drop down my pivot doesn;t refresh to "select all" so i have only product 1 and product 2 selected. so even if client B has product 3 available, the pivot doesn;t select it. Anything i can add so that my pivot refeshes everytime i select a new client from filter?

Nmarkit
08-30-2013, 03:28 AM
this is how my combobox is currently setup


Private Sub ComboBox1_Change()
Sheets("Dashboard").Range("E9").Value = Sheets("Dashboard").ComboBox1.Value
Sheets("PivotTrade").Range("B4") = Sheets("Dashboard").Range("E9").Value
Sheets("PivotAccount").Range("B1") = Sheets("Dashboard").Range("E9").Value
Sheets("PivotRevenue").Range("B2") = Sheets("Dashboard").Range("E9").Value
End Sub

p45cal
08-30-2013, 03:50 AM
Then you'll need something along the lines of:
ActiveSheet.PivotTables("PivotTable3").PivotFields("TYPE").ClearAllFilters
(or PT.PivotFields("TYPE").ClearAllFilters in my code) somewhere when the user changes the client.

Nmarkit
08-30-2013, 04:04 AM
thank you thank you thank you