Pivot table 'views'

[Chartist]

I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot which doesnt make sense.

Thanks

 

[jeffreyweir]

yep - that code alteration fixed it. Hooray. So now I guess the trick is to code in a filter via a dialog box that asks the user (me) what sites are to be included, get VBA to pull out the max and average of every column, then plot it. I guess this is all possible, with a bit of playing around and associated learning on my part. Your thoughts?

This would be a good time to open that Walkenback VBA power programming manual that's been sitting under my alarm clock for the past 6 months.

Or could just add subtotal functions with max and average enabled, then plot those results. (reason I use subtotal is in case I want to filter some data out via autofilter, and have graph adjust accordingly).

I guess there's no need to actually dump the data into a new sheet at all. This whole thing could run off a criteria box on the worksheet, with the results directly updating a graph on a new sheet. Way cool, and super fast.

Hey...Not a bad method of troubleshooting for a beginner...putting lots of 'ones' in to see if it was the enhanced 2007 functionality tripping the code up.

I still have to get your MS Query tip to work. Tried it on my work PC that has excel 2003, and got tripped up somewhere. 2007 filepath looks a little different, so not sure as yet how to find equivalent dialog boxes. But have purchased a book that has some info on MS query in excel 2007.

Thanks Fazza. So what kind of work do you do anyway, and how do you find time to do it and answer posts at the same time?

 

[Fazza]

Jeff,

Sounds like you're on the right path. Some good ideas there.

My work is in a large engineering office working with lots of data associated with cost estimates for large projects - 'playing' with Excel all the time. Sometimes I'm very busy, other times I have a little spare time. Using some smart methods in Excel I can do normal work super fast so that helps have some time to learn some more things.

It sounds like this little cross tab query has taken a more than one hour task to a less than 1 second job. It is great stuff.

Regards, Fazza