Results 1 to 3 of 3

Thread: Pivot link (xp)

  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot link (xp)

    Dear all,

    I have two pivot tables with a field call "date" in the Page (similar layout, same database).

    If I select a different date in this field, the date in the second pivot table should change to the same value. This should work in both directions.

    TIA

    Wolf

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot link (xp)

    Some of this question is "relatively easy" others are much more difficult.

    Perhaps others can chime in with some suggestions.

    You can add code to change all the pivot tables or even just some of the pivot tables to keep all the page fields identical. That is the easy part. [You would have to tell us which tables to change, all of them are would be the easy way, checking names would be a little more involved.

    The problem comes in determining when one is changed and which one.

    The "easiest" way would be to to create your own combobox that holds the dates of interest and when this was changed it would run the code to change all the pagefields. The only snag (and it is "minor") is tha you would have to have code to fill it with the unique values. XL does not store the list anywhere, it is generated at runtime. (the good news is that John Walkenbach has some code which can be easily adapted (he fills a listbox instead of a combobox)

    An alternative (and I have not figured out all the details, and to be honest am not sure if they can be worked out) is to try and trap an event. This is the snag (as far as I can see). Changing the page field does not trigger a change event! it will trigger a calc event and if all the pivots were on different sheets, you could have the calc event for each sheet (not the workbook one) get the page field for that sheet (whenever the sheet is calculated), and then replace all the others with it. It gets cumbersome since it would probably need a careful "balance" of enabling and disabling events and not changing it when it is already changed.

    It would be "simple" if you could "capture" the event, but like many of the builtin routines, (like filtering) there is no event excel allows you to capture.

    If you need more details on some of this let me know. I haven't tried to work thru any coding, but I am think the best way would be with the creation of a "global" page field to change them all and leave the individual ones to change them individually.

    Steve

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot link (xp)

    Hi Steve,

    thanks for your thoughts.

    I did it now the "pragmatic" way:
    I created a Button next to each Pivot Table, which is making the selection of the fields of both tables identical.
    It's not most elegant, but avoids the possible trouble with events.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •