Index-Match Data Validation List

See here:
http://www.excelforum.com/excel-form...82#post3374382
Otherwise post your workbook

This might help

http://www.contextures.com/xlDataVal02.html

Originally Posted by DinghoAteMyBaby

See attached.

D17 in Roster provides a single drop down item, where there should be 4.

Which of your tabs is "Roster"?

** Edited **

I apologize, I misread.

2013 is the tab in which D17 has a dropdown Data Validation.
It pulls data from sheet DTB, by category of position.

Based off of the position, I want the Data Validation drop down list to filter only those from DTB that meet the referenced position (i.e. C16)
and list any names that meet that criteria.

Per your PM request for guidance:

My work limits my upload / linking ability. The following link is to an issue I wrote and I apologize for seeking you out, directly, but I feel this is simply a syntax-based issue. I would greatly appreciate your help.

http://www.excelforum.com/excel-form...tion-list.html

The attachment on this link will show what looks like a Roster.
On Sheet '2013' there is a cell (D17) that uses Data Validation, Indexing and Matching against cell (C16) where the Position Filter is located.

I created a list of all the variables the filter would find and created the Data Validation list (located in sheet 'VLookup').

What I want to have, is the ability to select the position to filter and have the Data Validation drop down list return all the player names at that position (in Cell D17's Data Validation Drop down list). In this scenario, there should be 4.


If you are feeling generous, my next step is to have cells D18, D19, D20 (all the open slots) have this same ability, only to also filter out names that are in the cells already selected. More or less, so I couldn't select the same name 4 times... That once one is placed, I'd have only the remaining ones left. This part would mostly be aesthetic, but it would make managing 5 Fantasy Leagues of 12 Fantasy teams each, MUCH easier.


Thank you very much for your time,

There are a couple of options.

If you don't mind that the data validations for WR position all have same names, then it would be easier.

You would just apply this formula for the Data Validation (List) to all cells in the group:

=OFFSET(INDEX(DTB!$A:$A,MATCH($C$16,DTB!$A:$A,0)),0,1,COUNTIF(DTB!$A:$A,$C$16),1)

I have shown this option separately in in Z17 to Z21.

Your probable preferred option of removing already chosen names from the list is a bit more complex.

What you need to do is create a separate list in the DTB sheet for each position. I did one for the WR position in column AB.

You need a formula to "reduce" the list as names are chosen. So formula in AB2 is:

=IFERROR(INDEX($B$2:$B$50,SMALL(IF($A$2:$A$50=AB$1,IF(ISNA(MATCH($B$2:$B$50,'2013'!$D$17:$D$21,0)),ROW($B$2:$B$50)-ROW($B$2)+1)),ROWS($A$2:$A2))),"")

This is an array formula and so must be confirmed with CTRL+SHIFT+ENTER not just ENTER and then copied down. Note, I assumed a total list size of 50 rows, you may need to expand it, but don't use whole columns and try to keep the list to absolute min necessary.

Repeat these list for each position.

Now back in Sheet3, the Data Validition formula for D17 to D21 would be:

=OFFSET(DTB!$AB$2,0,0,COUNTIF(DTB!$AB:$AB,"?*")-1,1)

The other groups would be similar formula, only changing reference column accordingly.

Hope this helps.

Thank you, NVBC. That was exactly what I needed.
Works perfectly.

Great! You are welcome!