Two Excel data validation lists based on the value of one other

musoguy · May 18, 2011

Hi there. I am creating a workbook that allows the user to assign teachers to classes within multiple performing arts disciplines.

I have created a couple of tables in a data worksheet. The first one is the class list, so for example under the Dance discipline will be Jazz, Ballet etc. Under the Acting discipline will be Acting for Camera, Tools of Acting etc. And so on.

The second table has the same disciplines but contains the names of the staff who can teach them.

So in other words I have two tables with the same titles (the disciplines), but one is populated with subjects and the other with staff.

In my main worksheet I have a list of teaching periods. Under that is a drop down list of the disciplines from the data page. Under that I have created a drop down list based on the choice made on the disciplines list which is in C5 to list the classes available within that discipline. I did it using named ranges and then the formula:

=INDIRECT(SUBSTITUTE($C$5," ","_"))

I now need to create a second dependent drop down list in the cell under the first one. This list needs to name the teachers qualified to teach the discipline. The trouble is, I have already used the named ranges for the other dependent list, and this list also has to come from the choice made in C5. Is there any way to do this?

On the same subject, having done some research, it is my understanding that you cannot use a dynamic named range with the technique I used for the dependent list I created. Is that so? The problem is the user will be adding the data for the dependent list, and the length of the list may vary. If I just extend a regular named range for more cells than will ever be used, the list contains a number of blanks, which I can’t seem to get rid of with a named range instead of a specific range (i.e. $A$1:$A$100)


I hope this wasn’t too confusing, and that someone can help

Ammarbokhari · May 18, 2011

@musoguy:
Hi,
It seems you are looking for 3 drop down lists which depend on the data that you select in your first drop down choice.
If it is what you are looking for, then you have to use nested if function in your data validation list based on the choices you make.
If I am wrong please correct me.

musoguy · May 18, 2011

Thanks for the reply Ammarbokhari. It's actually two lists based on the choice made in a third. I did look into nested ifs, but my understanding is you can only have 7 or 8 arguments, and the list the other lists are based on has more than 8 options.

GlennUK · May 18, 2011

Can you name the teachers lists to be like the classes but with, say, "_T" appended to the names? Then you'd use something like:

=INDIRECT(SUBSTITUTE($C$5&"_T"," ","_"))

?

musoguy · May 18, 2011

That's brilliant! I think it should work. Thank you so much, will let you know tomorrow if it does.

GlennUK · May 18, 2011

I hope it does the job.

musoguy · May 18, 2011

Thanks again Glenn, it works perfectly, and now gives me unlimited dependent validation lists if I need them.

I don't suppose by chance you have a solution for the other question I had about using a dynamic named range for a dependent list?

Two Excel data validation lists based on the value of one other

musoguy

Board Regular

Excel Facts

Ammarbokhari

Board Regular

musoguy

Board Regular

GlennUK

Well-known Member

musoguy

Board Regular

GlennUK

Well-known Member

musoguy

Board Regular

Similar threads

Forum statistics

Share this page

Two Excel data validation lists based on the value of one other

musoguy

Board Regular

Excel Facts

Ammarbokhari

Board Regular

musoguy

Board Regular

GlennUK

Well-known Member

musoguy

Board Regular

GlennUK

Well-known Member

musoguy

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock