EXCEL: Dynamic Validation List

ouestque
ouestque used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Two different ways shown in attached workbook
Dynamic-list-dropdown.xlsx
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
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:
=FILTER(B2:B9,A2:A9=I2)

Open in new window

If this formula is in cell J2, you may create a data validation dropdown to return the grades using a Source formula of =J2#

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))),"")

Open in new window

The associated named range Dynamic has a Refers to of:
=Sheet2!$L$2:INDEX(Sheet2!$L$2:$L$9,SUMPRODUCT(--LEN(Sheet2!$L$2:$L$9)>0))

Open in new window

The associated data validation dropdown uses Source formula of =Dynamic

Author

Commented:
Thank you byundt. Awesome solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial