# Filter onto another sheet



## Adam19931 (Dec 30, 2022)

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 (Dec 31, 2022)

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.xlsmABCDEF1EEIDFull NameJob TitleDepartmentBusiness UnitGender2E02387Emily DavisSr. MangerITResearch & DevelopmentFemale3E04105Theodore DinhTechnical ArchitectITManufacturingMale4E02572Luna SandersDirectorFinanceSpeciality ProductsFemale5E02832Penelope JordanComputer Systems ManagerITManufacturingFemale6E01639Austin VoSr. AnalystFinanceManufacturingMaleData

Sheet Output looks like this without any option being taken in A2:
MrE_1225747_1616D10_filter onto another_221231.xlsmABCDE1Department234567OutputCells with Data ValidationCellAllowCriteriaA2ListAccounting;Engineering;Finance;Human Resources;IT;Marketing;Sales

After making a choice:
MrE_1225747_1616D10_filter onto another_221231.xlsmABCDEF1Department2Finance345EEIDFull NameJob TitleDepartmentBusiness UnitGender6E01070Leonardo MartinManagerFinanceSpeciality ProductsMale7E01639Austin VoSr. AnalystFinanceManufacturingMale8E00187Miles MehtaDirectorFinanceResearch & DevelopmentMale9E04088Ezra LiangVice PresidentFinanceResearch & DevelopmentMale10E01845Leo FernandezManagerFinanceResearch & DevelopmentMaleOutputCells with Data ValidationCellAllowCriteriaA2ListAccounting;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 (Jan 1, 2023)

Thanks that's worked perfectly!


----------

