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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,938
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Will you only have one data validation cell for each column, or will you want to have multiple rows?
 

Romzy

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,938
Office Version
  1. 365
Platform
  1. Windows
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
 

Romzy

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Surely there's a simpler solution that works with the single table of 3 columns like in the example I posted? :(
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,282
Office Version
  1. 2016
Platform
  1. Windows
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,"> "))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

Romzy

New Member
Joined
Jan 6, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Thanks for the responses - will try 'em out over the weekend!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,695
Members
414,088
Latest member
rodriboraun

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
Top