meerdere draaitabellen koppelen aan dropdownlists

[retteke]

Hallo,

Ik heb veel gezocht en getest maar mijn antwoord nog niet gevonden. wellicht wel al eens aan de orde geweest, maar net in een andere variant.


Ik ben via deze site wel uitgekomen op een engelstalig forum waar ik wel al een heel eind een voorbeeld gevonden heb dat in de buurt komt van mijn vraag. Eigenlijk is het heel simpel. Ik wil een aantal invoervelden (dropdowns) kunnen kiezen, vervolgens moeten alle draaitabellen verversen op basis van de gekozen invoervelden (filters). De voorbeelden die ik gezien heb, hebben slechts 1 invoerveld, en dus 1 draaitabelfilter. Ik heb echter meerdere filters nodig. Ik heb in de bijlage op sheet "sales Pivot" in cel D3 en D4 een dropdowngezet. Ik ben op zoek naar een code die ervoor zorgt dat als ik daar iets kies (en ook D2) in alle draaitabellen de draaitabelfilters deze waarden overnemen.

Wie kan me helpen?Bekijk bijlage PivotMultiPagesCellChange.xls

 

[cow18]

eigenlijk is dat "a real pain in the ass".
Ondertussen had ik een soort workaround ontwikkeld voor 1 paginaveld, maar nog nooit voor 3.
Dus bij deze heb je een 1e uitgave. Mits wat aanpassingswerk zou het in meer situaties kunnen werken, maar hier heb ik uitgegaan van een bereik waarin ik waarden kan kiezen, de gedefinieerde naam "MijnKeuzes". Die is 2 kolommen breed, met in de 1e kolom de naam van je paginavelden en in de 2e kolom kan je dan kiezen.
Nu ga ik er keihard van uit dat de pagerange exact dezelfde volgorde aanhoudt als "MijnKeuzes". Later zou dat minder dwingend kunnen geprogrammeerd kunnen worden. Maar ja, je bent een testcase ...
Als je nu iets kiest in "MijnKeuzes" wordt dat doorgekopieerd naar de pageranges van iedere draaitabel (als die netjes overeenkomen).
Hoe ben ik hierop gekomen ??? Soms weigert VBA, wat je ook probeert, pertinent iets in een pagefield te zetten, wat je ook probeert, maar als je het gewoon in de cel zet dan werkt het wel ... . Begrijpe wie het kan.
Dus bekijk het even en geef eens commentaar.
o, ja, niets kiezen = alle categorieën

Eigenlijk dacht ik dat als de draaitabellen met dezelfde cache werkten, dan de filters dan automatisch doorgevoerd werden, blijkbaar had ik dat mis.

 

[retteke]

Hoi Cow18!

Dit is exact wat ik bedoel, helemaal super!!! Ik had dit niet zelf kunnen bedenken! ik ga het nu in mijn document proberen te verwerken. Wellicht dat er nog een andere draaitabel bij komt die niet volgens dit principe aangepast moet worden. Maar dat zie ik dan wel weer, wie weet kom ik dan weer in de lucht.

Nogmaals enorm bedankt voor de oplossing en de snelle reactie

groetjes
Retteke

 

[cow18]

eigenlijk had ik niet getest of het op de klassieke manier fout ging.
Het was eigenlijk een vluggertje, dus heb ik het even meer op mijn gemak bekeken.
De klassieke manier gaat hier ook wel

 

[retteke]

Cow18,

Bedankt voor je update. Ik ben er nog niet helemaal uit waar de verschillen zitten. Ik zie wel dat je de code aangepast hebt, maar wat dat exact anders is durf ik niet vast te stellen.Volgens mij werken beide opties, welke adviseer jij om te gaan gebruiken?

Mijn dank is groot voor je hulp!!

vriendelijke groet,

Retteke

 

[cow18]

die laatste versie is flexibeler. Die zou ik gebruiken.

 

[pixcel]

@Cow: mooi opgelost.
@Retteke:
- waarom de extra stap met gegevensvalidatie? Je kunt de gebruiker toch ook in de paginafilters zelf laten kiezen? Scheelt een heleboel foutgevoelig gedoe met lijstjes en dynamische bereiken.
- maak van je data een Tabel/List, dan heb je geen dynamische bereiken nodig.

Overigens, in je voorbeeld heb je twee afzonderlijke gegevensbronnen (Thisworbook.Pivotcaches.count = 2). Dat zal wel een vergissing zijn. Maar het vraagt wel aandacht: vaak wil je pivottables met verschillende caches niet synchroniseren, ook al hebben ze overeenkomende fields. Daar houdt de code van Contextures helaas ook geen rekening mee.

 

[retteke]

Hoi pixcel,

De gegevensvalidatie is inderdaad een zwak punt, daar ben ik ook nog mee aan het worstelen. Het moet een onderhoudsvriendelijk bestand worden. Het voorbeeld is niet het werkelijke bestand dat ik ga gebruiken. Het is een bestand met nu 2, later 3, datadumps die ik aan elkaar moet knopen, waarbij ik telkens 2 overeenkomstige velden ( datum en user) moet vergelijken om uiteindelijk tot een gebruikersvriendelijke rapportage te komen. Op het rapportage tabblad dient de validatie nu om de juiste gegevens te presenteren. Het lastige daarbij, voor mij althans, is om uiterst links telkens de juiste users te laten tonen, afhankelijk van gemaakte keuzes. Ik zal morgen eens het werkelijke rapport met fake data hier plaatsen, wellicht dat je nog meer aanvullende tips hebt/ verbeteringen. In ieder geval alvast dank voor het meedenken!!

Groetjes, Retteke

 

[snb]

Een licht andere benadering: zie de bijlage

 

[cow18]

die lijsten moet je anders wel updaten als er nieuwe zaken worden toegevoegd.
o zou je anders ook kunnen werken met de gegevens die je uit de 1e draaitabel haalt

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim isect As Range, c As Range, it As PivotItem, s As String, s1 As String

  Set isect = Intersect(Target, Range("D2:D4"))
  If isect Is Nothing Then Exit Sub
  On Error Resume Next
  For Each c In isect.Cells
    s = ""
    With Sheets("sales pivot").PivotTables(1).PageFields(c.Offset(, -1).Value)
      For Each it In .PivotItems
        s1 = it.Name
        it.Delete
        If .PivotItems(s1).Name <> "" Then s = s & "," & s1
      Next
    End With
    If s <> "" Then c.Validation.Modify , , , Mid(s, 2)
  Next
End Sub

 

[snb]

die lijsten moet je anders wel updaten als er nieuwe zaken worden toegevoegd

Klopt; het gebeurt in ider geval elke keer als het bestand wordt geopend. Te oordelen naar het voorbeeldbestand lijken mij de lijsten niet superdynamisch (eerder het tegendeel).

Ik zie geen voordeel in de aanmaak van een objectvariabele die samenvalt met de range D2: D4 (die zelf al een object is).

Om het werkblad 'lists' overbodig te maken:

Private Sub Workbook_Open()
  For Each pf In Sheets("sales pivot").PivotTables(1).PageFields
    c01 = ""
    For Each it In pf.PivotItems
      c01 = c01 & "," & it.Name
    Next
    Range("C2:C4").Find(pf.Name, , , 1).Offset(, 1).Validation.Modify , , , Mid(c01, 2)
  Next
End Sub

 

[cow18]

daar kan ik mee leven.

 

[snb]

Een pak van mijn hart....

 

[retteke]

Allen,

Bedankt voor de vele reacties en het meedenken in oplossingen! Ik heb inmiddels een versie draaiend gekregen, met jullie input!!! Hij draait nu, ik ga eerst de gebruikerservaringen nu toetsen, afhankelijk daarvan ga ik het misschien nog perfectioneren.

Dank!!
Bij een volgende uitdaging meld ik me weer, onder het motto: beter goed gejat dan slecht bedacht