Filter onto another sheet

[Adam19931]

Hi all, I have a worksheet (sheet1) that contains multiple columns of data taken from an input form. I am looking for a formula or vba code that will allow the user to enter a week number into cell c3 on sheet2 and return all ID's from sheet1 (column A) that correspond to the inputted week number. These ID's are to be returned in cell C7 down on sheet2.

I am currently using a filter formula however this doesn't allow for sorts to be used on the data.

Thanks for your help in advance.


Sheet1

Sheet2

 

[HaHoBe]

Hi Adam,

maybe use the Advanced Filter and an event behind the sheet to trigger the event (my Excel is not able to read in the contents from pictures so I choose a different data set).

Sheet Data looks like this (only a small part of it)

MrE_1225747_1616D10_filter onto another_221231.xlsm
	A	B	C	D	E	F
1	EEID	Full Name	Job Title	Department	Business Unit	Gender
2	E02387	Emily Davis	Sr. Manger	IT	Research & Development	Female
3	E04105	Theodore Dinh	Technical Architect	IT	Manufacturing	Male
4	E02572	Luna Sanders	Director	Finance	Speciality Products	Female
5	E02832	Penelope Jordan	Computer Systems Manager	IT	Manufacturing	Female
6	E01639	Austin Vo	Sr. Analyst	Finance	Manufacturing	Male
Data

Sheet Output looks like this without any option being taken in A2:

MrE_1225747_1616D10_filter onto another_221231.xlsm
	A	B	C	D	E
1	Department
2
3
4
5
6
7
Output

Cells with Data Validation
Cell	Allow	Criteria
A2	List	Accounting;Engineering;Finance;Human Resources;IT;Marketing;Sales

After making a choice:

MrE_1225747_1616D10_filter onto another_221231.xlsm
	A	B	C	D	E	F
1	Department
2	Finance
3
4
5	EEID	Full Name	Job Title	Department	Business Unit	Gender
6	E01070	Leonardo Martin	Manager	Finance	Speciality Products	Male
7	E01639	Austin Vo	Sr. Analyst	Finance	Manufacturing	Male
8	E00187	Miles Mehta	Director	Finance	Research & Development	Male
9	E04088	Ezra Liang	Vice President	Finance	Research & Development	Male
10	E01845	Leo Fernandez	Manager	Finance	Research & Development	Male
Output

Cells with Data Validation
Cell	Allow	Criteria
A2	List	Accounting;Engineering;Finance;Human Resources;IT;Marketing;Sales

Data in Range starting with A5 can be sorted and will stay on sheet unless you make any other choice (data will be overwritten) or blank A2 (data starting with A5 will be cleared).

Code behind Sheet Output:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2")) Is Nothing Then
  If Not Target.Value = vbNullString Then
    Sheets("Data").Range("A1:N" & Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("A1:A2"), _
        CopyToRange:=Range("A5:N5"), _
        Unique:=False
  Else
    Range("A5").CurrentRegion.Clear
  End If
End If
End Sub

If your sorting will always be one column and directions this may be added to the code above.

Ciao,
Holger

 

[Adam19931]

Thanks that's worked perfectly!