asked on

Building Dynamic Lookup Dropdown Lists Using Excel

Hello Experts,

I have 2 master tables:
1- (Student: Student ID, Student Name)
2- (Course: Course ID, Course Name)

And have a third table that combined the previous 2 tables called (Student Registration Table) and contains 4 dropdown lists: Student ID, Student Name, Course ID, Course Name.

I want to build up an excel sheet for this combined table (Student Registration Table) so when I add a new record and select both "Student Name" and "Course Name "
the "Student ID" and "Course ID" dropdown lists will dynamically look up the corresponding ID's respectively.

Note: Student ID & Course ID dropdown list shouldn't be editable.

the Idea is explained below in the Image as well and sorry for my bad English.

Thanks a lot in advance.
Harreni

Roy Cox

Can you attach an example workbook

Rob Henson

If the ID columns aren't to be editable then they shouldn't be a dropdown list but should be a formula and then protected cells to prevent editing.

Finding an entry in a list is reasonably simple with the MATCH function:

=MATCH(Value,List,Match_Type)

Value - the student name or course name or reference to a cell containing it
List - The relevant list of names or courses
Match_Type - there are 3 options; -1, 0 and 1; I would suggest this would be 0 as you would want an exact match.

With a sample file we can put this in place for you.

Thanks
Rob

Harreni

ASKER

Thanks a lot experts for your prompt responses. Please check the attached file
StudentRegistrationTable.xlsx

Rob Henson

See attached updated file.

I have converted your lists to Tables, this allows excel functions to reference the table rather than a specified range.

I have then added MATCH function to the two columns on the combined table. As you add new entries to the student or course tables the range referred to in the combined table will extend. As you add entries to the combined table the formulas for the ID columns will extend down as required.

Thanks
Rob H
StudentRegistrationTable.xlsx

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

I had been working on updating the file when I spotted Shums post. I have now done similar in the attached file:

Data Validation in Combined table based on Lists created from Names and Courses columns in their tables. I haven't used Offset function like above, with a table you can refer to the Table and Column Name.

Reference for StudentList is
=Students[Studentname]

Reference for course list is
=Courses[CourseName]

The Data Validation then refers to StudentList and CourseList; unfortunately won't allow direct reference to the Table.

I have also added the IFERROR function to the formulas on the Combined Table. As the choice of Name and Course is from the Data Validation there is only the possibility of blank or valid entry so no need for the blank check and "Not Found" option.

Having said that, using a table does not add the Data Validation to the Name and Course columns until after an entry has been made so new entries won't be checked by the DV; unless you just enter a space to create the new row on the table and then use the DV to choose the correct name and course. Bit of a Catch 22 situation there unfortunately; use a table so that it expands as required but can't have the DV pre-populated, without the table you can have the DV pre-populated but what happens when you reach the end of pre-populated cells; you're back to having no DV to check the entry.
StudentRegistrationTable.xlsx

SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Harreni

ASKER

Thanks a lot Shums & Rob for your nice solutions and explanations.