Hello again,

I'm helping a co-worker on a survey project. We've been forced to put each question into it's own pivot table and we'd like to link them so that when survey type is chosen from page selections, all update. I've found a couple different macros which don't seem to work for me and he really doesn't want to use one if he can avoid it. He's using 2007 and I'm on 2010. I've also come across where someone did it accidentally by simply copying/pasting a pivot table to create a new one back in 2002/2003, but that doesn't seem to work in our newer versions.

Anyone able to help us out?

TIA,
ama

We're going to need a more specific definition. How is the data organized and what is the data's realationships to the pivot tables? What do you mean by "Survey Type" and where is that stored in the data and how does the user select it? Are the answers on separate sheets? Do they all have the same format (same columns and same column headings)?

Providing a sample report would make the question a lot clearer.

In the file, the pivots are set up on sheet4 and the data is on sheet3. All of the pivots are based on the same dataset and Survey_Name is a part of that set. Survey_Name is set up as each pivot's report filter. What else do you need?

ama

BTW, I'm working on a macro solution. I really just want to know if Excel has that ability built-in or not. His boss is convinced it's possible to link pivot tables to each other, but can't remember how.

I think the lightbulb is going off in my head. You would like to make one selection for survey name and have each of the pivot tables updated. Is that correct?

Yes sir

If you can afford to limit yourself to a single selection, the following will work

Mulitple selections is going to take a little more thought.

Quiet lunch hour and some lucky breaks. I think this will get what you want.

First the event that triggers off a change in Cell B1 on Sheet 4.


Then get information from the first pivot table and loop through the pivot tables on Sheet 4 and set the filters


I did one thing to your sample file. I eliminated all lines below row 30. I was picking up some stray data there. I also made a table out of it and made that the source of the pivot table, but carried it out inconsistently. It doesn't really matter as long as you have the same valid range feeding the pivot table.

There are a couple of caveats with this code. If you change the sheet name, you will have to change the code where indicated. Your first pivot table is named "Helpful." I use this pivot table to get the filter information. If the name changes, there are two places in the code where it must be changed.

I frequently use code similar to my first example where only one selection is needed. I have not, up until now, needed to update multiple pivot tables with multiple selections. I think it's only a matter of time before I will need this capability myself.

Thank you. That does work beautifully! I'm guessing, then, that there's no non-VBA option for linking pivot tables?

Not that I know of. Even in the simple case, where you only allow one selection, one would think that you could simply put =$B$1 in the other dropdown boxes. But the pivot table won't accept that. You can manually type in the value in lieu of clicking on and selecting a vaulue which is what the VB code for the simple case simulates.

There is another option and that is to "normalize" the source data. I can't think of a non-VB way of doing that either.

Now if the numbers that are your column headers are fixed, for example Reason for Contact is a code in the range of 0 to 7 for "No Reason Given," "Technical Support," "Licensing Issue" or whatever or Helpfulness is a scale of 0 to 10 then you can make a fixed format report that does not need pivot tables.

Simply enumerate all possible choices across the columns and then used COUNTIFS to compute the number of each category for the survey selected. If this is the case, I can give you a partial example of that, and you can extend it on your own. This would be a non-VB solution.

Rats. He's not a programming fan, but he really wants the functionality of the pivot table's ability to generate a report of a particular result's data. I really appreciate your help Dan, and your solution was much more elegant than what I was doing ;D.

Have a good weekend!

Well, here is the non-VB version anyway. Look at the Non-VB tab.

You most definitely want to change your source data into a table for this.

I use a pivot table in the tab Survey_List to get a cheap and dirty unique list of survey names. Column B is what actually runs the dropdown list. The first value is “*” which is the wildcard for all surveys. I repeated a formula all the way down to Row 150 and used COUNTIF in Cell E1 to get a count of all populated rows.

The dropdown list itself runs from a named dynamic range called Survey_List and it is defined as =OFFSET(Survey_List!$B$3,0,0,Survey_List!$E$1,1)

So what this does is give you an asterisk and a unique list of all surveys in Table_Survey.

On the report page, the magic formula is: =COUNTIFS(Table_Survey[Helpfulness],B4,Table_Survey[Survey_Name],$B$1)

This would be duplicated for each area of evaluation.

As an alternative you could also “condense” the report as shown in Rows 15 though 17.

If you really want to simulate a Pivot Table, you can put the string (All) in place of the asterisk and off to the side in a helper Cell use something like: =IF(B1 = “(All)”,”*”,B1) and use whatever this cell is in the formula.

Cool . Thank you Dan.

Just a note for the multiple selection macro: I tested it out by putting my data into the table over the sample data and it kept crashing. The error said it couldn't set the variable. Turns out the base pivot had retained the old pivot items for Survey. I went through the pivot tables and changed the pivot table options in the data tab so that # of items to retain per field was set to None. I probably only needed to set the base pivot, but better safe than sorry. It works flawlessly now. Even tweaked it to use a second report filter .

cheers and thanks again!
ama

I am glad it is working out for you and as I said previously, it's probably only a matter of time before I need this technology myself.