PivotTables in Excel

[MountainHawk]

I have two separate PivotTables, which are using the same dataset, but need to be sorted differently. I was wondering if it is possible to 'synch' the Page fields of the tables, so that as I change the first, the second updates as well.

Thanks.

[Tri4Ben]

Why don't you use a series of sumif statements instead of the pivot table. That way you only have to press F9 to update everything. I sometimes make the pivot table to get the table the way I want it then I paste all the numbers as values and use sumifs to get to the same numbers. This is especially useful because my coworker (who posts under the name Elisha) puts tons of pivot tables in all of our templates and they do not refresh properly when I change the data.

[MountainHawk]

I need to cycle through twenty states, and only the top 10 classes are shown for each state. The pivottable is ideal for this, because of the 'Top 10' feature in table options. Change the state in the pivottable, and the classes that are on the exhibit change.

[whisper]

I think you can create a VBA event macro and do this relatively easily, if your comfortable using VBA.

[Tri4Ben]

This may be a situation where you can either sort the data first and then do your sumifs, or you can make the first pivot table and then reference the second one off the first with sumifs.

You are probably better off just changing the state in both pivot tables that you had before. It would be pretty complicated to get the top 10 other wise.

You could try using the rank function. Make sure your last argument is 0 or else you will get the smallest classes. Then use the sumifs and have it reference a column with rank and state combined.

This is hard to explain, but I hope it helps.

[oldgirl]

I thought in Excel's data filter option, you can just show the top 10 or 20?

Just a thought.