Filters/Drop down on sales data with multiple selections

[schminki]

I want to be able to select all of these options on a cover sheet which i have created with data validation so that when a combination of these 8 elements are selected it spits out the data i need:

[TABLE="width: 173"]
<tbody>[TR]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Total Market[/TD]
[/TR]
[TR]
[TD]All Major Categories[/TD]
[/TR]
[TR]
[TD]All Brands[/TD]
[/TR]
[TR]
[TD]All Categories[/TD]
[/TR]
[TR]
[TD]All Subcategories[/TD]
[/TR]
[TR]
[TD]All Genders[/TD]
[/TR]
[TR]
[TD]Including Set[/TD]
[/TR]
</tbody>[/TABLE]

I want it to basically spit out a table of the sales data (value and units with growth by brand) using the dataset i have in a different tab which looks like this (just 1 line of the data -there are 30,000 items):

[TABLE="width: 1708"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Date[/TD]
[TD]Spend[/TD]
[TD]Customers[/TD]
[TD]Transactions[/TD]
[TD]Units[/TD]
[TD]BRAND[/TD]
[TD]MAJOR CATEGORY[/TD]
[TD]CATEGORY[/TD]
[TD]SUBCATEGORY[/TD]
[TD]GENDER[/TD]
[TD]Colour[/TD]
[TD]MASS /PRESTIGE[/TD]
[TD]Petrol/Non Petrol[/TD]
[/TR]
[TR]
[TD][101650444][/TD]
[TD]2016 6 - June[/TD]
[TD]8.74[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD] A[/TD]
[TD]Cars[/TD]
[TD]4 wheels[/TD]
[TD]SUV[/TD]
[TD]Female[/TD]
[TD]Black[/TD]
[TD]Prestige[/TD]
[TD]Petrol[/TD]
[/TR]
</tbody>[/TABLE]


I managed to make it work through creating combinations and a helper sheet when i only had 3 options (brand, major category and category) but now there are too many options and we are looking at thousands of combinations so can't do it... any help?

Thanks so much

 

[sokgat]

My suggest is to use pivot table.