Dynamic Sumifs for ALL criteria

So I am playing with a few ideas to try and help the performance of a particular dynamic report that consists of named ranges, indirect (and apparently you cant have dynamic named ranged in an INDIRECT formula, which was unexpected) then applying 6 filter criteria.

Here is an example of a formula:

Please Login or Register  to view this content.

This formula allows the user to control everything with data validation through drop downs. The search criteria are linked to a drop down, and the drop down has all the possible choices AND and "ALL" possibility, which just chooses "*" and renders that portion of the SUMIFS useless. This works but slows down when I apply the entire data set of 500,000 to 1,000,000 rows. Basically do any of you have any ways I can make this process more effecient?

I originally thought about having nested IF formulas but then realized that nests and formula would be huge and I am not completly sure if that would help anything because of the volatile INDIRECT portion of the formula. I also tried to see if I could come up with any clever solution using choose, because from what I understand it only calculates the chosen formula.


Can anyone offer any advice?

I am thinking it might be time for me to spend some more time in the VBA world and use active-X controls to control pivot tables to allow some much less complex formulas to grab the data from the pivot table.

For some reason I am having difficulty attaching a file, but I will attach it when I can get it to work correctly.

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Please Close this thread.
http://www.excelforum.com/excel-form...-criteria.html

Here is the template. The Dashboard in question is on the Dashboard tab. If you have any questins, let me know.


Also, to give a little bit of background information. I basically have too much data and have to pull it into PowerPivot first, then aggregate and get it all lined up to be pasted into this template. The more recent version of the template has a few macros to automatically apply new named ranges to ONLY capture the number of rows used on the DATA tab and then I am working on trying to use pivot tables as a single column for the data validation instead of static lists, that way they are also automatically updated.

If you have any advice on any of that, feel free to let me know.

Thanks!

Apparently I can't upload on this laptop (I think it needs to be reimaged...) so I will try from another computer in a bit.

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.