Setting up dynamic tables

[Pasha81]

I'm not sure if this is the right area to post this

Now I don’t think I need a macro for this. Just some advice on setting it up efficiently in a Pivot table. I have data of revenues for say 4 years which I will use to create 4 different tables. One table will show only data from 2006, another will show 2006 + 2007, another 2006 + 2007 + 2008, and the last one will show all 4 years revenues combined.
But here’s the tricky part, I don’t want to see all tables at once. I want to be able to select which table I want, and produce the table in the same ‘range’ on the spreadsheet.
Any ideas on how this could be achieved? I’m trying with pivot tables but its not working.


many thanks

[DonkeyOte]

It's not very clear:

a) all tables are of the same size & dimension
b) all tables share the same field names etc
c) what the dimensions of said table(s) are (ie how big we're talking)
d) why specifically a Pivot Table ?

You could in theory do the following

1 - create 4 named ranges (1 per table)
2 - have a cell above the PT in which you can select from 1 of the 4 specified ranges (via a Validation List)
3 - have a further named range _PTData which uses =INDIRECT(B1) [where B1 is the DV cell)
4 - set the PT Source to be _PTData

... you would still need to manually refresh the table as and when you alter the DV selection.

If you wanted to automate the PT refresh you would need VBA, you could perhaps use INDIRECT to create the single view but at that point you lose PT functionality of course.