EXCEL: Dynamic Validation List
I have an Excel file with 2 sheets.
Sheet1 has a table with over 50k records in the following format:
ID. Grades
221. 89
221. 80
221. 49
005. 20
007. 30
221. 100
005. 80
007. 40
On Sheet2 Column A a user will enter an ID. How do I create a validation list in column B so that a list appears based the value in column A?
Example1: If a user enters "007" in A2, then the validation list in B2 is:
30
40
Example2: If a user enters "221" in A3, then the validation list in B3 is:
89
80
49
100
Sheet1 has a table with over 50k records in the following format:
ID. Grades
221. 89
221. 80
221. 49
005. 20
007. 30
221. 100
005. 80
007. 40
On Sheet2 Column A a user will enter an ID. How do I create a validation list in column B so that a list appears based the value in column A?
Example1: If a user enters "007" in A2, then the validation list in B2 is:
30
40
Example2: If a user enters "221" in A3, then the validation list in B3 is:
89
80
49
100
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In the workbook attached above, the first method uses the new FILTER function. Users with Excel 2016 on an Office 365 subscription will get that feature (comes with dynamic arrays) when it is released. For the time being, however, you need to be an Office Insider to get it.
Assuming ID and Grade are in A2:B9 and the desired student ID is in I2, you may use this formula in a single cell to return all grades:
Simplicity is the reason why you need to be aware of this method, even if you can't use it right now.
The old-fashioned (and brute force) way of doing it is to create a dynamic named range returning the grades. I returned the grades using:
Assuming ID and Grade are in A2:B9 and the desired student ID is in I2, you may use this formula in a single cell to return all grades:
=FILTER(B2:B9,A2:A9=I2)
Simplicity is the reason why you need to be aware of this method, even if you can't use it right now.
The old-fashioned (and brute force) way of doing it is to create a dynamic named range returning the grades. I returned the grades using:
=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/($A$2:$A$9=$I$2),ROWS(L$2:L2))),"")
=Sheet2!$L$2:INDEX(Sheet2!$L$2:$L$9,SUMPRODUCT(--LEN(Sheet2!$L$2:$L$9)>0))
Premium Content
You need an Expert Office subscription to comment.Start Free Trial