Reproduce Pivot Table with Function

[robhumbug]

I need to reproduce the functionality of a pivot table without using pivot tables. I'd much rather do this with functions rather than macros (easier for me to understand), but if a macro is the only way to go, please provide that solution.

Attached sheet explains the situation best. I'll have one data set, from which dozens of different tables will be produced. This data will be deleted and replaced periodically, so dynamic formulas are needed.

It's icing on the cake if I can 1) set up a drop-down selector that allows me to dynamically change the horizontal and vertical parameters and 2) put the data in descending order.

Thanks for the help! Happy to answer any clarifying Q's.

[DonkeyOte]

Happy to answer any clarifying Q's.

First things first - why no Pivots ?

If it's because the dataset will be physically deleted and replaced then you have similar issues using formulae, no ?

Assuming the above to indeed be the case then...
To prevent #REF! errors with formulae you would need to use INDIRECT to create the various range references.
At that point all functions become Volatile and I suspect you will find overall calculation performance to be pretty poor.

[robhumbug]

The biggest reason I'm trying to avoid pivot tables is refresh rate. I'm going to have dozens of tables pulling from data that has 40K+ lines. Even if I use macros to automate the process, it will take a very long time to update.

The ultimate product isn't going to be tables, but bar charts based on those tables. With so many pivot tables and charts, the file size starts becoming prohibitive...

Never heard of the volatility problem before (sounds like I'd have the same issue as above). If we can avoid the need for dynamic formulas by simply clearing the sheet rather than "deleting" the cells, that should work just fine.

I should also add that I'd like to have the ability to, for example, only show data for Product A, where I can see the project count by year and region as shown in the first table, but only for those projects pertaining to Product A.

If I'm completely missing something here, I'm open to more efficient ways of approaching the problem.

[DonkeyOte]

My point would simply be that the formulae necessary to ape the flexibility of a Pivot Table/Chart will be such that performance is likely to be poor - esp. when working with large data sets.
If as implied by profile you're using XL2003 you have additional issues regards formulae efficiency and multi conditional calculations - ie helper cells would become something of a no-brainer in performance terms

Without knowing how you intend to group etc it might be the case that the greater majority of the Pivots can share a single cache which would help in terms of restricting file size.