Slicers in Excel with Sub Category - Help Please

[jevi]

Hi there,

I am looking on the internet for slicers with sub categories, check even the pivot tables slicers but it sound I can't understand.

I need a first slicer with 5 products and than I need below each product another slicer with the sub names. Smth like below:

So I need the 1 slice with the 5 products and then I need other 5 slices with each sub product, because then I need to show a table of calculation. Is it possible?
[TABLE="width: 727"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SLICE 1[/TD]
[TD]SLICE 2[/TD]
[TD]SLICE 3[/TD]
[TD]SLICE 4[/TD]
[TD]SLICE 5[/TD]
[/TR]
[TR]
[TD]PRODUCT 1[/TD]
[TD]PRODUCT 2[/TD]
[TD]PRODUCT 3[/TD]
[TD]PRODUCT 4[/TD]
[TD]PRODUCT 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]THE PRODUCTS HAVE SUB CATEGORIES OF PRODUCTS[/TD]
[/TR]
[TR]
[TD]PRODUCT 1.1[/TD]
[TD]PRODUCT 2.1[/TD]
[TD]PRODUCT 3.1[/TD]
[TD]PRODUCT 4.1[/TD]
[TD]PRODUCT 5.1[/TD]
[/TR]
[TR]
[TD]PRODUCT 1.2[/TD]
[TD]PRODUCT 2.2[/TD]
[TD]PRODUCT 3.2[/TD]
[TD]PRODUCT 4.2[/TD]
[TD]PRODUCT 5.2[/TD]
[/TR]
[TR]
[TD]PRODUCT 1.3[/TD]
[TD][/TD]
[TD]PRODUCT 3.3[/TD]
[TD]PRODUCT 4.3[/TD]
[TD]PRODUCT 5.3[/TD]
[/TR]
[TR]
[TD]PRODUCT 1.4[/TD]
[TD][/TD]
[TD]PRODUCT 3.4[/TD]
[TD]PRODUCT 4.4[/TD]
[TD]PRODUCT 5.4[/TD]
[/TR]
[TR]
[TD]PRODUCT 1.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]PRODUCT 4.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]PRODUCT 4.6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
THANK YOU SO MUCH

 

[mart37]

Maybe you can use: Dependent Validation Lists

 

[jevi]

but it won't give the data as I want to click on product 1, and then click on one of the sub products of product 1 so I can have the calculations.

 

[mart37]

Can you give an example of your calculation table?

 

[SpillerBD]

As for the slicers, I would think you only need to set the Option for the Sub-Categories slicer to "Hide values with no data"

 

[jevi]

Hi Mart,

Thinking carefully and working last night I think one slice with all the subcagories is fine, because the name of them is so long so I can't do as I said before even I can find the way. But as I tried I think there was no way.

Still I am sending the calculation formula.

[TABLE="width: 435"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Product Name[/TD]
[TD]SubProdcut Name[/TD]
[TD]Total[/TD]
[TD]Months[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.1[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.3[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.4[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD]Product 1.5[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]Product 2.1[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD]Product 2.2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]Product 3.1[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]Product 3.2[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]Product 3.3[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD]Product 3.4[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[jevi]

Thank u Spiller but all subcategories have data, still I found the way as I said the name was too long so is not worth having the subcategories for each product on different slicer. Still I see the the slicer are a little but rigid even though I love them.

 

[SpillerBD]

The point would be that selecting "Product 3" in the product slicer would reduce the number of options in the "Subproduct slicer" making that selection much easier.
I have seen the desire to use slicers for a field with too many options, but by using intersecting fields, makes the end choice manageable.

 

[jevi]

But do you know Spiller why the slicer main like when I choose Product 3, it moves place product three and it goes at the first slice and does't stay at the third slice as I would like. I don't like to change them place the main products. I did also a custom list and it sound is not working. What do you think? I think i did everything from the slicer settings.