Populating Team Lists based on a master list of players

Question

I have a master list of players that can be on 1 of 4 teams. I want to create separate lists on another tab that references the master list. I want a list for all players on team 1 on the master list and include their name age and gender. Once i have for Team 1, i want to do the same for team 2, 3, and 4.

essentially, if the master list says someone is on Team 1, i want that players info to automatically populate to the team 1 list on another tab.

Appreciate any help.

Answer 1

Hello Kevin N,

I'm Shalom and I'd happily help you with your question. In this forum, we are Microsoft consumers just like yourself.

You can achieve this in Microsoft Excel using formulas and data validation. Let’s break it down step by step:

Master List Setup: Create a master list of players with columns for their name, age, gender, and team assignment. For example: | Name | Age | Gender | Team | |------------|-----|--------|------| | Player 1 | 25 | Male | 1 | | Player 2 | 30 | Female | 2 | | Player 3 | 22 | Male | 1 | | ... | ... | ... | ... |

Team Lists on Another Tab: Create a new tab (worksheet) where you want to display the separate team lists. In the first row, label the columns as “Name,” “Age,” and “Gender.”

Auto-Populating Team Lists: For each team list, you can use the INDEX and MATCH functions to retrieve the relevant player information from the master list. Let’s assume your master list is in Sheet1, and the team lists are in Sheet2. In the team list for Team 1 (Sheet2), enter the following formula in cell B2 (assuming the team name is in cell A1): =INDEX(Sheet1!$A$2:$D$50, MATCH($A$1, Sheet1!$D$2:$D$50, 0), COLUMN())

Drag this formula down to populate the entire team list for Team 1. This formula looks up the team number (1, 2, 3, or 4) in the master list and retrieves the corresponding player information. Data Validation (Optional): To make it user-friendly, you can set up data validation drop-downs for the team names (e.g., “Team 1,” “Team 2,” etc.) in cell A1. When a team name is selected, the team list below will automatically update based on the chosen team.

Here’s a breakdown of the formula in cell B2:

INDEX(Sheet1!$A$2:$D$50, MATCH($A$1, Sheet1!$D$2:$D$50, 0), COLUMN()) Sheet1!$A$2:$D$50: The range of the master list (adjust as needed). MATCH($A$1, Sheet1!$D$2:$D$50, 0): Finds the row where the team number matches the selected team (from cell A1). COLUMN(): Retrieves the corresponding column (Name, Age, Gender).

Remember to adjust the cell references and ranges based on your actual data.

Best Regards, Shalom

Answer 2

Hi,

You should be able to use the FILTER() function.

Answer 3

Shalom,

Thank you for your reply! I am running into an issue while trying to set this up.

I am able to use your formula to grab the first player's information, however, when i drag down the formula, it shows the same one players information on all of the lines. Let me know if you know what I'm doing wrong.

Really appreciate it!

Answer 4

Hi,

pic

.

1. data in A2:D24
2. in cell G1 drop down menu list 1-2-3-4
3. in cell F3 write:

=FILTER(A2:D24,D2:D24=G1,"")

press Enter key