data validation select values from another table on condition

jillrein

New Member
Joined
Nov 4, 2011
Messages
9
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,282
Office Version
  1. 2016
Platform
  1. Windows
Hi JillRein,

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

JillRein.xlsx
ABCDEFGHIJK
1Student NumberNameCourseWorktable
2S87JimPotteryPottery
3S92SusanSurgeryNameDVFarming
4S45VanessaFarmingSarahPotterySurgery
5S12SarahPottery 
6S88BertSurgery 
7S32JohnFarming 
8S87SarahFarming 
9S92SusanPottery 
10S45VanessaPottery 
11S12SarahSurgery 
12S88BertPottery 
13S32JohnPottery 
Sheet2
Cell Formulas
RangeFormula
K2:K13K2=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
CellAllowCriteria
H4List=OFFSET($K$2,,,COUNTIF($K$2:$K$20,"> "))
 

jillrein

New Member
Joined
Nov 4, 2011
Messages
9
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

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

1607556313257.png


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

New Member
Joined
Nov 4, 2011
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,282
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1Student NumberNameCourse
2S12JimPottery
3S27SusanSurgeryStudent NumberNameCourseWorkarea
4S32VanessaFarmingS32VanessaFarmingFarming     
5S08SarahPotteryS27SusanPotterySurgeryPottery    
6S11BertSurgeryS08SarahFarmingPotteryFarmingSurgery   
7S02JohnThatchingS11BertSurgerySurgeryPottery    
8S08SarahFarmingS32VanessaFarmingFarming     
9S27SusanPotteryS02JohnWeldingThatchingSmithingPotteryFarmingWeldingCarpentry
10S02JohnSmithing       
11S08SarahSurgery       
12S11BertPottery       
13S02JohnPottery       
14S02JohnFarming       
15S02JohnWelding       
16S02JohnCarpentry       
3rd
Cell Formulas
RangeFormula
K4:P16K4=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:G16G4=IF(F4="","",INDEX($B$2:$B$9999,MATCH(F4,$A$2:$A$9999,0)))
Cells with Data Validation
CellAllowCriteria
H4:H16List=OFFSET($K4,,,,COUNTIF($K4:$P4,"> "))
 
Solution

jillrein

New Member
Joined
Nov 4, 2011
Messages
9

ADVERTISEMENT

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

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,282
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1Student NumberNameCourse
2S12JimPottery
3S27SusanSurgeryStudent NumberNameCourseWorkarea
4S32VanessaFarmingS12JimFarmingPottery     
5S08SarahPotteryS27SusanPotterySurgeryPottery    
6S11BertSurgeryS08SarahFarmingPotteryFarmingSurgeryPottery  
7S02JohnThatchingS11BertSurgerySurgeryPottery    
8S08SarahFarmingS32VanessaFarmingFarmingFarming    
9S27SusanPotteryS02JohnWeldingThatchingSmithingPotteryFarmingWeldingCarpentry
10S02JohnSmithingS11BertSurgeryPottery    
11S08SarahSurgery       
12S11BertPottery       
13S02JohnPottery       
14S02JohnFarming       
15S02JohnWelding       
16S02JohnCarpentry       
17S08JohnPottery       
18S32VanessaFarming       
3rd-Dupe
Cell Formulas
RangeFormula
K4:P18K4=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:G18G4=IF(F4="","",INDEX($B$2:$B$9999,MATCH(F4,$A$2:$A$9999,0)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F4:F9999Expression=COUNTIF($F$4:$F4,F4)>1textNO
G4:G9999Expression=SUMPRODUCT((COUNTIF(K4:P4,K4:P4)-1)*(K4:P4<>""))>0textNO
Cells with Data Validation
CellAllowCriteria
H4:H18List=OFFSET($K4,,,,COUNTIF($K4:$P4,"> "))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,694
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