data validation select values from another table on condition

[jillrein]

I would be very grateful if someone can help me. I have two tables, one enrollment table with student number, student name, course. The other table tracks their progress on the courses. I want to have a data validation on the second table that lists all the courses from the enrollment table that a particular student is enrolled on so if I put in student no 1 and he is enrolled on maths and English, only those two options will be in the drop down. Ideally, it would also default to the course if that student is only on 1 course. I can't work out how to do it.

 

[Toadstool]

Hi JillRein,

You will need to construct the list from courses matching the name, like this:

JillRein.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Student Number	Name	Course								Worktable
2	S87	Jim	Pottery								Pottery
3	S92	Susan	Surgery				Name	DV			Farming
4	S45	Vanessa	Farming				Sarah	Pottery			Surgery
5	S12	Sarah	Pottery
6	S88	Bert	Surgery
7	S32	John	Farming
8	S87	Sarah	Farming
9	S92	Susan	Pottery
10	S45	Vanessa	Pottery
11	S12	Sarah	Surgery
12	S88	Bert	Pottery
13	S32	John	Pottery
Sheet2

Cell Formulas
Range	Formula
K2:K13	K2	=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($B$2:$B$9999)-ROW($B$1)/($B$2:$B$9999=$G$4),ROW()-ROW($K$1))),"")

Cells with Data Validation
Cell	Allow	Criteria
H4	List	=OFFSET($K$2,,,COUNTIF($K$2:$K$20,"> "))

 

[jillrein]

Thank you so much. This is exactly what I want to do except that now I need to do it for multiple rows. I converted the list in cols G and H into a table and changed the formula to use table1[@name] in place of $G$4 in the hope that it would work for the current row by changing the list dynamically but that failed. Do you know how I can make it work for subsequent rows?

 

[Toadstool]

I'm not sure I'm following correctly? So are you looking to enter a list of names then select from an LoV for each, looking like this?

The column K selection is dynamic for each name so such a solution would need to be dynamically build horizontally for each name.
Tell me the maximum number of courses you'll ever have and I'll give you a sample.

 

[jillrein]

Your understanding is exactly what I want. There would never be more than 6 courses for any 1 student. Thank you so much for taking your time to help me.

 

[Toadstool]

So columns K to P are where the dynamic lists are built for each row of Student Number entered. You can hide these columns if you want.

I've noticed you said you'd enter Student Number so I've changed the selection and added a column to retrieve the Student Name for verification when entering the data.

You can see that each row of column H has the LoV selection but pulls it from the correct data validation list in columns K to P.

JillRein.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Student Number	Name	Course
2	S12	Jim	Pottery
3	S27	Susan	Surgery			Student Number	Name	Course			Workarea
4	S32	Vanessa	Farming			S32	Vanessa	Farming			Farming
5	S08	Sarah	Pottery			S27	Susan	Pottery			Surgery	Pottery
6	S11	Bert	Surgery			S08	Sarah	Farming			Pottery	Farming	Surgery
7	S02	John	Thatching			S11	Bert	Surgery			Surgery	Pottery
8	S08	Sarah	Farming			S32	Vanessa	Farming			Farming
9	S27	Susan	Pottery			S02	John	Welding			Thatching	Smithing	Pottery	Farming	Welding	Carpentry
10	S02	John	Smithing
11	S08	Sarah	Surgery
12	S11	Bert	Pottery
13	S02	John	Pottery
14	S02	John	Farming
15	S02	John	Welding
16	S02	John	Carpentry
3rd

Cell Formulas
Range	Formula
K4:P16	K4	=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($B$2:$B$9999)-ROW($B$1)/($A$2:$A$9999=$F4),COLUMN()-COLUMN($K$3)+1)),"")&""
G4:G16	G4	=IF(F4="","",INDEX($B$2:$B$9999,MATCH(F4,$A$2:$A$9999,0)))

Cells with Data Validation
Cell	Allow	Criteria
H4:H16	List	=OFFSET($K4,,,,COUNTIF($K4:$P4,"> "))

 

[jillrein]

This is exactly what I need. Thank you so much. You have saved my from another evening trying to solve this.

 

[Toadstool]

You're welcome!
...and here's a couple of Conditional Formats to enhance your data entry.
Column F will highlight if you enter the same Student Number twice.
Column G will highlight if a Student Number has the same course selected more than once.

JillRein.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Student Number	Name	Course
2	S12	Jim	Pottery
3	S27	Susan	Surgery			Student Number	Name	Course			Workarea
4	S32	Vanessa	Farming			S12	Jim	Farming			Pottery
5	S08	Sarah	Pottery			S27	Susan	Pottery			Surgery	Pottery
6	S11	Bert	Surgery			S08	Sarah	Farming			Pottery	Farming	Surgery	Pottery
7	S02	John	Thatching			S11	Bert	Surgery			Surgery	Pottery
8	S08	Sarah	Farming			S32	Vanessa	Farming			Farming	Farming
9	S27	Susan	Pottery			S02	John	Welding			Thatching	Smithing	Pottery	Farming	Welding	Carpentry
10	S02	John	Smithing			S11	Bert				Surgery	Pottery
11	S08	Sarah	Surgery
12	S11	Bert	Pottery
13	S02	John	Pottery
14	S02	John	Farming
15	S02	John	Welding
16	S02	John	Carpentry
17	S08	John	Pottery
18	S32	Vanessa	Farming
3rd-Dupe

Cell Formulas
Range	Formula
K4:P18	K4	=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($B$2:$B$9999)-ROW($B$1)/($A$2:$A$9999=$F4),COLUMN()-COLUMN($K$3)+1)),"")&""
G4:G18	G4	=IF(F4="","",INDEX($B$2:$B$9999,MATCH(F4,$A$2:$A$9999,0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4:F9999	Expression	=COUNTIF($F$4:$F4,F4)>1	text	NO
G4:G9999	Expression	=SUMPRODUCT((COUNTIF(K4:P4,K4:P4)-1)*(K4:P4<>""))>0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H4:H18	List	=OFFSET($K4,,,,COUNTIF($K4:$P4,"> "))

 

[jillrein]

Thank you again. I do hope you are around next time I get stuck. Have a lovely weekend.