Dynamic Pivot Tables

[Chuck6475]

Okay, as you can tell from my number of posts, I'm new to this, but the results I've gotten have been OUTSTANDING.

Here is my question:

Software: Excel 2003 W/7 with destination machine Excel 2007 XP.

This is a golf tournament scoring program that handles a wide variety of tournament style. Two of those tournaments are diametrically opposed to one another. In a Stableford tournament the winner has the HIGHEST score, while in a typically tournament the winner is the lowest score.

I use Pivot Tables for everything and in an ideal world I would like to use the same pivot table for both of these tournaments. I can do that manually by changing the "data source" and changing the sort/ top "X" places with Pivot Table wizard, BUT I would like to do it dynamically.

Given I know when this is a Stableford tournament or standard, is there a way to either change the data source for the pivot table or given the data source is a named array, change the reference of the named array or name itself to reflect the tournament format (Stableford or normal).

If the above is possible, can the sort order / items displayed by the pivot table be changed too. Whatever combination works the idea is to not show the 3, 4 or 5 lowest score but instead the 3, 4, or 5 highest scores.

Thanks in advance.

 

[Michaels]

Hello !

Are you looking to make you pivot table dynamic based on dynamic data source ? If "YES", then covert your source data into Table (by clicking in any cell of the source data and pressing CTRL + T). If asked you table has a header and if it does, click ok. Now whatever you add in your main table, which is also the source data for your pivot table, it will be added automatically to the pivot table (after pressing ALT + F5, it refreshes the pivot table).

Also you can create multiple pivot tables on the basis of same data source.


Post back if this doesn't help.

 

[Chuck6475]

Shawn thanks for the reply. I believe I understand your suggestion but that isn't quite my question.

There are two tables - rawscores and Stableford. They are identical except for the data. In the Stableford scoring system a "par" which might have been 4 is a 0. An "Eagle" instead of being "3" is 5, etc.. In fact all of the data is taken from the "rawscores" and converted in Stableford scores in the Stableford table.

Now I have a series of Pivot Tables (6) in fact, one for each flight in the tournament, that work using "rawscores" for input and report the "Top 4 or 5" golfers and scores. In an ordinary tournament the lowest score wins, however in the Stableford tournament the highest score wins.

Now it is easy enough to have two sets of Pivot Tables one for each format, but I was looking to see if I could change the data source in a Pivot Table by selecting a tournament type, i.e. if it is "stableford" to have the pivot table reference the stableford scores instead of the rawscores.

Now if I could do that, then I would need to address the sort sequence of the pivot table to make it descending instead of ascending.