Help creating dependant drop-down list NOT using INDIRECT

[Bleached Lizard]

Hi all,

I need to create a data validation drop-down that is dependant on what has been selected in another drop-down. However, all the solutions I've come across online so far suggest using the INDIRECT function, which is not appropriate in my case. This is because the data upon which the two drop-downs are based on is itself dynamic. My specific situation is this:

In one sheet, I have a list of university names in one column, and in the next column a list of faculties. The same university can appear multiple times in the sheet (once for each different faculty at the university). This information will be added to regularly, with new universities and new faculties added often.

In another sheet, I want a drop-down in one column that takes its validation from the list of universities. I then want a second drop-down that is only populated with a list of faculties at that university. The INDIRECT solution seems to only work if I can define a static range of cells from which to draw this data. How do I do it in my situation above?

Many thanks!

[Glenn Kennedy]

No. You don't need indirect. This will work - the example goes down to 4 levels, but you'll only need two which will simplify it. You will need to be very familiar with setting up named ranges to make this work, however!!

Finally, you'll also need to create a unique list of Universities and of dependent departments somewhere to act as the source. That's not difficult either.

Obviously, if you were to post an example of your data (as a spread sheet) it would be easier to work with than just your words...

[JBeaucaire]

'Dynamic Cascading Dependent Drop Boxes

Typically Dynamic Named Ranges and Cascading Dependent Drop Down boxes using INDIRECT() references to prior DV cells simply does not work. You can do one or the other, but not both. Here is a technique for creating cascading dependent drop boxes where the secondary lists ARE created dynamically, compatible with Excel 2003+.

Dynamic Cascading Data Validation

There is a sample workbook showing it in action.

[Bleached Lizard]

Hi again,

Thanks for those answers. I tried the method laid out in the link provided by JBeaucaire and it seemed to work to a degree, but not quite to the level I needed. Is there any way to do it without having a sheet full of the unique lists? This is because, using this method, I would have to duplicate the information that is already on the sheet in another sheet but just formatted in the form of the required unique lists. Basically, I already have a worksheet that in one column contains the names of universities, then in the next column has the faculties at those universities, with each row having a specific uni/faculty combination. So for example, the data might look like this:

London Metropolitan | Faculty of Law
London Metropolitan | Faculty of Computing
London Metropolitan | Business School
Lancaster University | Faculty of Social Sciences
Lancaster University | Nursing School
Edge Hill University | Faculty of Education

Etc, etc. Can Excel use this sheet to determine which faculties there are at each university and generate dynamic dependant drop-down lists based on whether the user selects "London Metropolitan" or "Lancaster University" in another drop-down list?

If there's no easy way to do it, is it at least possible to have Excel generate the unique lists that have already been suggested so that the user doesn't have to duplicate information each time a new faculty is added (excel just automatically adds the new faculty to the dynamic named ranges)?

Many thanks for any help.