Generate lists based on Column information

[excelnewbie08097]

Hi there,
I have spent hours and hours searching without luck, and therefore and reaching out to the online community! I have tried everything I could find but still am unable to do what I need and, at this stage, doing it Manually would save me time and energy - but I hope you all can help me!
The goal: create 2 drop down menus on a sheet based on criteria entered onto a different sheet (same workbook).

I removed the personal data for sharing, but as you can see the Competition is already sorted. What I would like is a formula that will read the competition column, and then give me a list of All teams in the same competition. I would then use this formula to create a data validation list - (If Competition 1 is selected, only teams A,B,C,&D will show up, etc.) more rows will be added, so something dynamic would be great (or I can select more rows in the array too).
For reference - I am using Excel Mac 365 - I do not have an option for Windows. Therefore, Pivot table didnt work (no option to use DAX)
I have tried INDIRECT, IFERROR, INDEX, and XLOOKUP, all give me the first value but not a full list , of ALL teams that are linked to "1", "2", "3", and "4" respectively. I was hoping for something like UNIQUE does when reading a column.
What HAS WORKED - creating a separate "lists" sheets and pulling the data straight from there. The problem, whenever a user enters new data to the "source" sheet, I then need to copy and paste it to the lists sheet.
I know the solution must be simple... and due to that, it's driven me more insane :D - please help!

 

[Cubist]

Maybe this. Don't know what your actual data is like.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L
1	Team	Competition			1	2	3	4		selection	2
2	A	1			A	E	I	M
3	B	1			B	F	J	N
4	C	1			C	G	K	O
5	D	1			D	H	L	P
6	E	2
7	F	2
8	G	2
9	H	2
10	I	3
11	J	3
12	K	3
13	L	3
14	M	4
15	N	4
16	O	4
17	P	4
Sheet4

Cell Formulas
Range	Formula
E1:H1	E1	=TOROW(UNIQUE(B2:B17))
E2:H5	E2	=FILTER($A$2:$A$17,$B$2:$B$17=E1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
L1	List	=INDIRECT(CHOOSE(K1,"E2","F2","G2","H2")&"#")

 

[excelnewbie08097]

Hi there,

The first part I think is very similar to the solution I ended up finding - however for the Data validation, couldn't quite get it to work, and might be a big task if the data is over 100 different "competitions".

Here is what a user on reddit helped me achieve - I think its the best I have found, but happy to hear from others if there are better solutions out there!

 

[JerrExcel]

Hi,

Refer to this: Create a dynamic dependent drop down list in Excel an easy way
This is the closest solution I found. You can try to edit the formulas as to fit your columns and rows.


Cheers.

 

[excelnewbie08097]

Hi,

Refer to this: Create a dynamic dependent drop down list in Excel an easy way
This is the closest solution I found. You can try to edit the formulas as to fit your columns and rows.


Cheers.

Hi There, yes this is what I have tried, but is not quite there as it is always dependant on referring to the same row. I need the formula to change dynamically with new selections in each row, without needing to write a new formula each time. The video above is the solution, by creating a seperate spill list for each result

thanks though

 

[Cubist]

How about this?

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	Team	Competition			1	2	3	4		4	O
2	A	1			A	E	I	M
3	B	1			B	F	J	N
4	C	1			C	G	K	O
5	D	1			D	H	L	P
6	E	2
7	F	2
8	G	2
9	H	2
10	I	3
11	J	3
12	K	3
13	L	3
14	M	4
15	N	4
16	O	4
17	P	4
Sheet4

Cell Formulas
Range	Formula
E1:H1	E1	=TOROW(UNIQUE(B2:B17))
E2:H5	E2	=FILTER($A$2:$A$17,$B$2:$B$17=E1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
K1	List	=INDIRECT(CELL("address",XLOOKUP(J1,E1#,E2:H2)) &"#")

 

[JerrExcel]

Hi There, yes this is what I have tried, but is not quite there as it is always dependant on referring to the same row. I need the formula to change dynamically with new selections in each row, without needing to write a new formula each time. The video above is the solution, by creating a seperate spill list for each result

thanks though

Hi there,
Thanks for the video!
I am currently using excel 2016, which I can't use formula "SORT", "FILTER", "UNIQUE", and can't put "#" in the data validation input.

Below is my workaround (following the video shared above), this might be useful for those using excel 2016 or older version:

Step 1: Create table for data
Select the Data range, click "Format as Table"

Step 2: Create Helper table to get unique "Competition" list
Use a new sheet ("Dashboard"), choose a column to list unique "Competition", header "Competition (unique)"
=IFERROR(INDEX(Table1[Primary Competition],MATCH(0,COUNTIF(Dashboard!$I$2:I2,INDEX(Table1[Primary Competition],0)),0)),"")
Drag formula down.

Step 3: Create dropdown for "Competition" - use Data Validation - List
On dropdown menu table, input Header "Primary Competition"
Data validation, select "List", select range from Step2

Step 4: Create SPILL table to get Nth match for "Team" in one competition
On the right side of dropdown menu table, add column "Checking" for validation later, and add a few columns for Nth Match ("Team" from "Competition" chosen) to the right (ie, 1, 2, 3, 4, etc)
Use formula to populate list of team in the chosen "Competition".
=IFERROR(INDEX(Table1[Team],SMALL(IF(Table1[Primary Competition]=$B5,ROW(Table1)-MIN(ROW(Table1))+1),E$4)),"")
Remember to CTRL+SHIFT+Enter for this formula,
Drag formula down & right

Step 5: Create dropdown for "Team" - use Data Validation - List
On dropdown menu table, input "Team" as another header on right side of "Primary Competition"
Data validation, select "List", input =$E5:$J5 (using the range of cell for "Nth match")

Step 6: Checking using "Countif"
Columns "Nth Match" will change dynamically whenever Dropdown for "Competition" changed.
Hence, use these columns to count if the "Team" is existing in the selected "Competition".
Return "Check Team" if the team is not existed in the selected competition.
Formula =IF(C5="","",IF(COUNTIF(E5:J5,C5)=0,"Check Team",""))
Also add conditional formatting to "Team" dropdown menu. (Red when it is not exist)

Step 7: (optional) Hide column "Checking" and Hide columns for "Helper table"
Hide from column D until column L
For my example, I have tried to add new competition & team in Table1, and it populated in the dropdown.

Thanks!

20240422.xlsx
	B	C
1	Team	Primary Competition
2	A	Competition 1
3	B	Competition 1
4	C	Competition 1
5	D	Competition 1
6	E	Competition 2
7	F	Competition 2
8	G	Competition 2
9	H	Competition 2
10	I	Competition 3
11	J	Competition 3
12	K	Competition 3
13	L	Competition 3
14	M	Competition 4
15	N	Competition 4
16	O	Competition 4
17	P	Competition 4
18	Q	Competition 5
19	R	Competition 5
20	S	Competition 5
21	T	Competition 5
22	U	Competition 5
23	V	Competition 5
Reference

20240422.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	DropDown Menu											Helper table
2												Competition (Unique)
3					Nth Match							Competition 1
4	Date	Primary Competition	Team	Checking	1	2	3	4	5	6		Competition 2
5	23/4/2024	Competition 1	A		A	B	C	D				Competition 3
6	24/4/2024	Competition 3	A	Check Team	I	J	K	L				Competition 4
7	25/4/2024	Competition 2	E		E	F	G	H				Competition 5
8	26/4/2024	Competition 2	F		E	F	G	H				Competition 6
9	27/4/2024	Competition 3	J		I	J	K	L
10	28/4/2024	Competition 6	W		W
11	29/4/2024	Competition 4	M		M	N	O	P
12	30/4/2024	Competition 5	U		Q	R	S	T	U	V
13
14
15
16
17
18
19
20
21
22
Dashboard

Cell Formulas
Range	Formula
E5:J12	E5	=IFERROR(INDEX(Table1[Team],SMALL(IF(Table1[Primary Competition]=$B5,ROW(Table1)-MIN(ROW(Table1))+1),E$4)),"")
D5:D22	D5	=IF(C5="","",IF(COUNTIF(E5:J5,C5)=0,"Check Team",""))
L3:L22	L3	=IFERROR(INDEX(Table1[Primary Competition],MATCH(0,COUNTIF(Dashboard!$L$2:L2,INDEX(Table1[Primary Competition],0)),0)),"")&""
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5:C22	Expression	=$D5="Check Team"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B5:B22	List	=$L$3:$L$22
C5:C22	List	=$E5:$J5