Filter Teams by Selected Division with a Click of a Button

Are you tired of manually updating team lists based on divisions? Let’s automate this process in Excel. In this tutorial, we’ll cover how to filter and paste teams into your list by selecting the division from a dropdown menu.

The Problem: Manual Team List Updates

Manually maintaining an updated list of teams can be tedious and error-prone—especially if you have multiple divisions. This process is time-consuming, making it harder to maintain accuracy across large datasets or when frequent updates are needed.

The Solution: Automating with Excel Formulas & VBA

Luckily, there’s a straightforward way to automate this using the FILTER function and some basic VBA in Excel. Below is our step-by-step guide:

Step 1: Setting Up Your Sheets

Create your main sheets: Name one sheet “Selection Sheet” for dropdown selection, another named “Teams List”, and a final one called “Selected List”.
“Teams List”, containing two columns: Division names in column A (A2:A20) & Team Names in Column B (B2:B20). For example:

Division | Teams Div 1 | Team One, Div Two, etc.

Step 2: Adding a Dropdown on the “Selection Sheet”

The dropdown allows users to select which division’s teams they want displayed. To create this:

Select cell C31 in your ‘Selection Sheet’
Go to Data → Data Validation

Allow: List
Source (Comma separated list of divisions): Div 4,Div 5,Div X etc.

Step 3: Creating the Filter Formula on “Selected List” Sheet

=FILTER('Teams List'!B2:B10,'Teams List'!A2:A10='Selection Sheet'!C31,"")

Explanation:
– This formula dynamically pulls team names from the “Teams List” where the division matches what’s selected in cell C31 of your ‘Selection Sheet’. If no match is found, it will return an empty string.

The VBA Button Solution

If you’d prefer to trigger this dynamically via a button:

Go back into the “Selected List” sheet and insert a new module for our macro. (Use Alt + F11)

Sub PasteTeams() 'Define worksheet variables Dim wsSelection As Worksheet, wsTeamList as WorkSheet


Set Selection = ThisWorkbook.Sheets("Selection Sheet")

Set Teamlist = ThisWorkbook.sheets(“teams list”)

set selectedlist = thisworkbook.sheets(‘selected List’)
selectedList.Range(‘A2:A10’).ClearContents

If IsEmpty(Cells(c31, “selection sheet”).Value) Then MsgBox "Select a Division first!", vbExclamation: Exit Sub
'Copy team names based on selected division using advanced filter:

Teamlist.Sort Teamlist.range("a"&Lrow+2 & ":b"&lRow+2), , 1, Header:=xlNo

wsSelection.Range("A:A").AdvancedFilter Action:=xlfiltercopy, CriteriaRange:="A30", CopyToRange:=SelectedList.Range("A2")

End Sub

Explanation:

The VBA macro checks if a division has been selected.

Extra Tip: Automate with a Form Control Button

How?

Right-click your “Selected List” sheet and Insert → Shape.
Choose ‘Rectangle’ and position it on the screen as you desire. Select this shape, right click to assign macro “PasteTeams” from our VBA code above

When clicked:
– The button will execute PasteTeams, updating Column A with all team names for selected division.

That’s a wrap on filtering your teams by divisions. Using both FILTER formulas and some basic VBA can greatly simplify maintaining dynamic lists based off selection criteria.

The Conclusion

Key takeaways:

Use the dropdown for easy division selection in “Selection Sheet”.
Apply formulas to filter and display only relevant teams automatically based on selected criteria.
Enhance with VBA buttons if you need a more dynamic user interface.

The combination of Excel’s native filtering capabilities, combined with the power of VBA macros makes this an elegant solution for keeping your lists updated without manual intervention. For even more advanced automation features in Excel check out CelTools:

Don’t forget to experiment and tweak the solutions presented here to suit any specific needs of your project.

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical