Dependent drop down list mayhem with sample file link

[dlwearl]

Hello,

Looking for some help with dependent drop down lists.

File: https://drive.google.com/file/d/0BzcmD6CWA_HheHVPYUpoTVpNNGc/edit?usp=sharing

There are three sheets: the two that matter are Degree Audit, and Sheet1.

What I need is a drop down list in 'Degree Audit'!A27 that changes depending on what is chosen in 'Degree Audit'!A21 or 'Degree Audit'!A24. Sheet1 contains the lists that need to be placed in A21 and A24, along with the conditional choices for A27.

In a nutshell:
1) Students require 9-10 credits of humanities and fine arts classes to graduate, three classes total.
2) One class in humanities (cell A21 in Degree Audit), one class in fine arts (Degree Audit A24); the lists for the first classes are in columns A and H on sheet1.
3) The third class can be from either group.

There are exclusions in both the humanities and fine arts depending on which class was selected initially. For a humanities example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]If student selects [class] in A21...[/TD]
[TD]...then exclude from drop down in A27[/TD]
[/TR]
[TR]
[TD]HISTO-2205[/TD]
[TD]HISTO-2220 and HISTO-2235[/TD]
[/TR]
[TR]
[TD]HISTO-2220[/TD]
[TD]HISTO-2205 and HISTO-2235[/TD]
[/TR]
[TR]
[TD]HISTO-2235[/TD]
[TD]HISTO-2205 and HISTO-2220[/TD]
[/TR]
</tbody>[/TABLE]

For a fine-arts example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]If student selects [class] in A24...[/TD]
[TD]...then exclude from drop down in A27[/TD]
[/TR]
[TR]
[TD]ART-2212[/TD]
[TD]ART-2213[/TD]
[/TR]
[TR]
[TD]ART-2213[/TD]
[TD]ART-2212[/TD]
[/TR]
</tbody>[/TABLE]

Because A27 can be excluding either humanities or fine arts, I also need some way to fill in "Humanities" or "Fine Arts" in cell A26 depending on what the student elects to do; I suppose this is a radio button that picks between different dependent lists, but I'm not sure how to link that up.

Any help greatly appreciated!!!
David

 

[dlwearl]

Anybody on this?

David