Log In
Register
Home
December 7, 2021
Offline
The attached XLS shows a user-generated list of teams and players.
- Each player is on a team
- A team may be active or inactive (as indicated by the "Include in Droplist" fields)
- A player may also be active or inactive
- If an active player is on an inactive team, then the player is considered inactive
As per the guidance I received on this forum, I use pivot tables to restrict the selection of teams and players to only those that are active.
If a user picks a player, it's no problem to look up that player's team.
The problem is how to show which players belong to a selected team:
- All players on a selected team
- Active players on a selected team
- Inactive players on a selected team
Thanks!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Shawn,
Change the pivot structure to display the team name, not just the team member.
The pivot should look like this:
Team Player
Cubs Bill
Cubs Mary
Tigers Sally
With a formula like this one below, you'll be able to get the list of players of the selected team.
=OFFSET(D1,MATCH("Cubs",D:D,0),1,COUNTIF(D:D,"Cubs")) (where column D is the one where the Team column of the pivot table is located.
December 7, 2021
Offline
Thank you for your suggestion I'm sorry, but I am still struggling to make this work. Please see the updated XLS attached.
In your example, "Cubs" is hard-coded, so I replaced this with a reference to the cell in which the "TEAM" droplist selection is made by the user. This is "I7" in the new XLS.
The "TEAM" column of the pivot table is actually located on "Droplists!$D$4," so I replaced your "D" with that reference.
So, your original formula:
Becomes this new formula:
=OFFSET(Droplists!$D$4,MATCH(I7,Droplists!$D:$D,0),1,COUNTIF(Droplists!$D:$D,I7))
The problem is that I always get a blank list.
As always, thanks for your help.
1 Guest(s)
