Trying to create a drop down from VLookup Return or Group Values in Drop Down

**rebeccajp76** · 08-08-2011, 06:00 AM

Trying to design a spreadsheet so that manual data entry cells are minimized.

So far I have set up multiple Vlookup in row (which may not be the best alternative, so feel free to suggest otherwise) so that the Course Code can be entered in D2 pulling information from 'Course' sheet and returning the result from column B (sheet 'Course') into E2 on 'Current'. I need the results for the entire row on 'Course' sheet so I have repeated this on 'Current' in each cell ie if I entered 4901AA in D2, I want the corresponding row from 'Course' sheet that matches 4901AA to be copied into E2, F2, G2, H2 on 'Current'

So my first issue was to pull corresponding rows into the spreadsheet. Now that I have done this I would like to create a dependant drop down list of schools ('Current' Column L), but instead of creating a list of all the schools I want these to be refined by Faculty ('Current' Column G) ie if the Faculty is Science (G2) I would like the drop down box in School (L2) to limited to only the schools in the Faculty of Science (sheet 'Schools D2:D7). But as the result in Faculty is a vlookup return I am not sure this will be a workable option.

Alternatively I was thinking that a drop down could be created for course code as these are unique & range is grouped by Faculty (ie 4901AB to 4801BL is Science) and this could then determine which schools are visible in the drop down in 'Current' column L. But I am at a loss as how to group a range in the first drop down so that the dependant drop down box will activate to show only the schools for corresponding Faculty.

Summary:
1. I wanted to pull corresponding row information for respective unique course codes from 'Course' table into 'Current'. I have done this with multiple Vlookup formulae (there maybe a better alternative however it is not my priority unless it interferes with the second step)

2. My main priority is that I want to have a drop down for School column L which is dependant on the Faculty or Course Code, whichever is easiest and most straight forward.

I have possilby made this far too complex and confusing, but I hope someone can help me!

**TMS** · 08-08-2011, 07:15 AM

Step 1

Cell E2: =VLOOKUP($D2,Course!$A:$E,COLUMN(E2)-3,FALSE)

Drag across and down

Step 2

Data Validation:
Allow: List
Source: =INDIRECT($G2)

Regards