data filters

[aarti_rto]

Hello Experts,

i have below data , "Name , Department & salary".

i need your help to apply filter in "D" with dropdown for Department i.e. if i select HR then below table should display only HR related details..

D
A	B	C
Name	Department	Salary
ABC	SCM	193​
SAD	HR	418​
DSGFGF	HR	697​
DSAFDSGF	SCM	614​
SAF	HR	258​
AFDS	FI	136​
ADF	SCM	273​
GF	FI	753​
RET	SCM	119​
ET	SCM	786​
ETR	FI	286​
RYT	FI	431​

​

 

[Ezguy4u]

aarti let's get the ball rolling. Now usually this will generate more questions than answers, but we need to start somewhere. So is this what you had in mind.

22-02-08.xlsx
	A	B	C	D	E	F
1	Name	Department	Salary	Department	Name	Salary
2	ABC	SCM	193	FI	AFDS	136
3	SAD	HR	418		GF	753
4	DSGFGF	HR	697	SCM	ETR	286
5	DSAFDSGF	SCM	614	HR	RYT	431
6	SAF	HR	258	FI
7	AFDS	FI	136
8	ADF	SCM	273
9	GF	FI	753
10	RET	SCM	119
11	ET	SCM	786
12	ETR	FI	286
13	RYT	FI	431
Data

Cell Formulas
Range	Formula
E2:E13	E2	=IFERROR(INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13=$D$2,ROW($D$1:$D$12)),ROWS($D$1:D1))),"")
F2:F13	F2	=IFERROR(INDEX($C$2:$C$13,SMALL(IF($B$2:$B$13=$D$2,ROW($D$1:$D$12)),ROWS($D$1:D1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=$D$4:$D$6

 

[aarti_rto]

hi,
thank you for the reply , but i do not wish to create separate columns i.e. E & F.
i need the data to be filtered in same "A to C" range when filter applied in D .is it possible?

 

[Peter_SSs]

Perhaps you mean this instead?

D1 has a Data Validation drop-down where you choose a Department.
A1:C?? has been manually set up with AutoFilter.

aarti_rto.xlsm
	A	B	C	D
1	Name	Department	Salary
2	ABC	SCM	193
3	SAD	HR	418
4	DSGFGF	HR	697
5	DSAFDSGF	SCM	614
6	SAF	HR	258
7	AFDS	FI	136
8	ADF	SCM	273
9	GF	FI	753
10	RET	SCM	119
11	ET	SCM	786
12	ETR	FI	286
13	RYT	FI	431
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D1	List	FI,HR,SCM

You can then try this Worksheet_Change event code to filter/unfilter as cell D1 is changed. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D1")) Is Nothing Then
    If IsEmpty(Range("D1").Value) Then
      ActiveSheet.AutoFilter.Range.AutoFilter Field:=2
    Else
      ActiveSheet.AutoFilter.Range.AutoFilter Field:=2, Criteria1:=Range("D1").Value
    End If
  End If
End Sub

Here is the sheet after I have chosen a value from the D1 drop-down.

aarti_rto.xlsm
	A	B	C	D
1	Name	Department	Salary	HR
3	SAD	HR	418
4	DSGFGF	HR	697
6	SAF	HR	258
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D1	List	FI,HR,SCM

 

[Ezguy4u]

aariti because you can never have too many solutions, let's look at this one.

First make sure you select in the range, then select the Insert tab, then select Table

Now you should see Create Table. Make sure My table has headers is checked.

Now you have a table you can filter. Note Table Design if you want to change colors.

When you click the down arrow you get to select which department you want.

Here is my choice. Note Department has the filter.

 

[aarti_rto]

hi,
thank you for the reply , but i do not wish to create separate columns i.e. E & F.
i need the data to be filtered in same "A to C" range when filter applied in D .is it possible?

Perhaps you mean this instead?

D1 has a Data Validation drop-down where you choose a Department.
A1:C?? has been manually set up with AutoFilter.

aarti_rto.xlsm
	A	B	C	D
1	Name	Department	Salary
2	ABC	SCM	193
3	SAD	HR	418
4	DSGFGF	HR	697
5	DSAFDSGF	SCM	614
6	SAF	HR	258
7	AFDS	FI	136
8	ADF	SCM	273
9	GF	FI	753
10	RET	SCM	119
11	ET	SCM	786
12	ETR	FI	286
13	RYT	FI	431
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D1	List	FI,HR,SCM

You can then try this Worksheet_Change event code to filter/unfilter as cell D1 is changed. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D1")) Is Nothing Then
    If IsEmpty(Range("D1").Value) Then
      ActiveSheet.AutoFilter.Range.AutoFilter Field:=2
    Else
      ActiveSheet.AutoFilter.Range.AutoFilter Field:=2, Criteria1:=Range("D1").Value
    End If
  End If
End Sub

Here is the sheet after I have chosen a value from the D1 drop-down.

aarti_rto.xlsm
	A	B	C	D
1	Name	Department	Salary	HR
3	SAD	HR	418
4	DSGFGF	HR	697
6	SAF	HR	258
14
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D1	List	FI,HR,SCM

Thank you...