Thread: Reproduce Pivot Table with Function

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.

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.