Forum

Notifications
Clear all

Multiple Data Validation with Filter

4 Posts
2 Users
0 Reactions
179 Views
(@novusexcel)
Posts: 67
Estimable Member
Topic starter
 

Looking for suggestions.  Prefer no VBA.  I have one column that is a data validation list (Column A, Teams).  Based upon the team selected from the Teams list, I need another data validation list to populate with their available position list (Column B, Position).  For example, the position of Catcher has a requirement of needing 1 and that team already has a Catcher, I do not want catcher to show in the data validation list in Column B, Position).  If the position of Outfield has a requirement of 4 and that team as 2, I need Outfield to show in the data validation list as available in Column B, Position. 

Open to any suggestions.  Thanks.  

 
Posted : 19/02/2020 11:31 pm
(@purfleet)
Posts: 412
Reputable Member
 

Morining

Could you add an example workbook so that we do not have to recreate the data and to ensure that we understand what we are looking at?

Purfleet

 
Posted : 20/02/2020 12:17 am
(@novusexcel)
Posts: 67
Estimable Member
Topic starter
 

See attached actual file.  On the first tab, Input, when a team is entered (Column A), I need the Pos column (Column E) to only show their available roster positions based on the maximum position limits within the league. 

 

The position limits are as follows:

C=1

1B=1

2B=1

3B=1

SS=1

CI=1

MI=1

OF=5

UT=2

P=9

Min=10

 

When a position limit has been reached, I would no longer like that position to show in the data validation list for the Pos column (Column E).  I only want the available positions to show based upon the limits above.  Thanks. 

 
Posted : 20/02/2020 7:59 am
(@novusexcel)
Posts: 67
Estimable Member
Topic starter
 

I was able to make this work with a combination of Powery Query (Get & Transform Data) and using INDIRECT with Data Validation.  

 

I'm sure there are more efficient ways but at least it works.  

 
Posted : 21/02/2020 6:16 pm
Share: