Hide and Unhide rows based on dropdown list

[rahiljaved]

Hi
I have tried various codes but either it just hide and never unhide when the value change from dropdown or either it doesn't work.
Situation: I have a dropdown list (Cell B32) have 5 different categories in that and based on that list my data populates in table (A52:D100) using Index and match function at the table. In last row of the data (row 101) I have subtotal of the table. Now, I when I select B32, I want the data to be changed which it does for example: when the cell value shows "Category A" in cell B32, the data range from A52:D60 shows but my susbtotal stays at row 101 down below. I want the rest of empty rows from A61:D100 hidden and when I select "Category B" in Cell B32, it shows data from A52:D54 and again the subtotal rows stays at 101 which I want to come up to row 55 by hiding rest of empty rows. Please let me know if you have a solution.

Thanks in advance!

 

[rahiljaved]

New Microsoft Excel Worksheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Select	d											data	200				data
2													data	300				dd
3													data	500				d
4	Data	Value											data	600
5	d	4											data	700
6	d	5											data	500
7	d	2											data	600
8	d	1											data	400
9													data	800
10													data	900
11													dd	60
12													d	4
13													d	5
14													d	2
15													d	1
16													dd	50
17													dd	40
18													dd	30
19	Total	12											dd	20
20													dd	40
21													dd	10
22
23
24
25
26
Sheet1

Cell Formulas
Range	Formula
A5:A18	A5	=IFERROR(INDEX($M$1:$N$21,SMALL(IF($M$1:$M$21=$B$1,ROW($M$1:$M$21)),ROW()-4),1),"")
B5:B18	B5	=IFERROR(INDEX($M$1:$N$21,SMALL(IF($M$1:$M$21=$B$1,ROW($N$1:$N$21)),ROW()-4),2),"")
B19	B19	=SUBTOTAL(9,B5:B18)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=$R$1:$R$3

 

[rahiljaved]

New Microsoft Excel Worksheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Select	d											data	200				data
2													data	300				dd
3													data	500				d
4	Data	Value											data	600
5	d	4											data	700
6	d	5											data	500
7	d	2											data	600
8	d	1											data	400
9													data	800
10													data	900
11													dd	60
12													d	4
13													d	5
14													d	2
15													d	1
16													dd	50
17													dd	40
18													dd	30
19	Total	12											dd	20
20													dd	40
21													dd	10
22
23
24
25
26
Sheet1

Cell Formulas
Range	Formula
A5:A18	A5	=IFERROR(INDEX($M$1:$N$21,SMALL(IF($M$1:$M$21=$B$1,ROW($M$1:$M$21)),ROW()-4),1),"")
B5:B18	B5	=IFERROR(INDEX($M$1:$N$21,SMALL(IF($M$1:$M$21=$B$1,ROW($N$1:$N$21)),ROW()-4),2),"")
B19	B19	=SUBTOTAL(9,B5:B18)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B1	List	=$R$1:$R$3

This is a sample data that I have created not the actual sheet.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Hide and Unhide rows based on dropdown list
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.