Data Validation Dropdown where the source data is not grouped into columns or lists

Romzy

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
This one has been a real melon-scratcher for me, hoping someone can help me figure this out.

Context :
There are 20 football teams, and 4 types of players - GKP, DEF, MID & FWD
All the data is present in one single table under the 3 headers

Requirement :
If I select the Position and Team name, I'd like to find the potential results as a dropdown in the third cell.
As illustrated in the example below, I'd like to see D9 and D12 show up as a drop-down for me to choose in I7

1609962415314.png


Note -
The data source comes in the format above and includes about 450+ names across the 20 teams.
I thought of concatenating the Position and Team name to use as a unique identifier but I'm struggling to get the matches show up as a drop-down (data-validation)

Appreciate any assistance that you can offer!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hi & welcome to MrExcel.
Will you only have one data validation cell for each column, or will you want to have multiple rows?
 
Upvote 0
Thank you Fluff! Wish you a wonderful 2021!

Not sure if I understood the question correctly, I think multiple rows.
This is what my input screen would look like -
1609970279357.png


I've already defined the first criteria and the entry is Column C would be one of 20 options.
Accordingly, I'd like to get the desired results in Column D (based on my two criteria)

Hope that clarified things and thank you for your assistance!
 
Upvote 0
In that case you would probably be better off creating separate ranges of player names for each team/position & then using those in your final drop down by the use of indirect & concatenating the values from cols B & C
 
Upvote 0
Surely there's a simpler solution that works with the single table of 3 columns like in the example I posted? :(
 
Upvote 0
Hi Romzy,

You could build the list of matches off to one side and use that as your LoV.

Romzy.xlsx
BCDEFGHIJK
2POSITIONTEAMPLAYER
3MIDTOTTom
4DEFEVEBen
5FWDLIVJim
6DEFTOTLilyPOSITIONTEAMPLAYERPlayLoV
7MIDTOTSueMIDTOTTomTom
8MIDLIVKenSue
9MIDTOTMayMay
10 
Sheet1
Cell Formulas
RangeFormula
K7:K10K7=IFERROR(INDEX($D$3:$D$9999,AGGREGATE(15,6,ROW($D$3:$D$9999)-ROW($D$2)/(($B$3:$B$9999=$G$7)*($C$3:$C$9999=$H$7)),ROW()-ROW($K$6))),"")
Cells with Data Validation
CellAllowCriteria
I7List=OFFSET($K$7,,,COUNTIF($K$7:$K$9999,"> "))
 
Upvote 0
Here is one way.

Formula in F33 is copied down column F. The results in columns G, H, ... will automatically spill across whatever columns are required.
You can subsequently hide columns F: .... if you want
Select D33:D?? and in Data Validation select List with Source as shown (=F33#)


Romzy.xlsm
BCDEFGHI
1
2POSITIONTEAMPLAYER
3MIDTOTTom
4DEFEVEBen
5FWDLIVJim
6DEFTOTLily
7MIDTOTSue
8MIDLIVKen
9MIDTOTMay
10DEFEVEDon
11
12
31
32POSITIONTEAMPLAYER
33MIDTOTTomSueMay
34DEFEVEBenDon
Sheet1
Cell Formulas
RangeFormula
F33:H33,F34:G34F33=TRANSPOSE(FILTER(D$3:D$10,(B$3:B$10=B33)*(C$3:C$10=C33),""))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
D33:D34List=F33#


Example result:

1610100484550.png
 
Upvote 0
Thanks for the responses - will try 'em out over the weekend!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top