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?

SLICE 1	SLICE 2	SLICE 3	SLICE 4	SLICE 5
PRODUCT 1	PRODUCT 2	PRODUCT 3	PRODUCT 4	PRODUCT 5
THE PRODUCTS HAVE SUB CATEGORIES OF PRODUCTS
PRODUCT 1.1	PRODUCT 2.1	PRODUCT 3.1	PRODUCT 4.1	PRODUCT 5.1
PRODUCT 1.2	PRODUCT 2.2	PRODUCT 3.2	PRODUCT 4.2	PRODUCT 5.2
PRODUCT 1.3		PRODUCT 3.3	PRODUCT 4.3	PRODUCT 5.3
PRODUCT 1.4		PRODUCT 3.4	PRODUCT 4.4	PRODUCT 5.4
PRODUCT 1.5			PRODUCT 4.5
			PRODUCT 4.6

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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.