Change data validation dropdown list using radio buttons

[dlwearl]

Hey everyone,

I'm having a problem with data validation drop down lists.

I'm making a degree audit sheet in Excel. I have a radio button on the degree audit sheet with two choices: A.A. and A.S.

I have data validation drop down lists for classes in physical science and life science which need to change depending on which degree option is selected via radio button.

The course information for both sciences are stored in separate tables on a different sheet, two tables for the physical and life science requirements for the A.A., and two tables for the physical and life science requirements for the A.S. Each table has two columns: one column with course ID numbers, the other with credit amounts.

When the radio button for A.A. is selected, I need the drop down lists for physical science and life science, located on the degree audit sheet, to display the corresponding course names from the A.A. tables, located on a separate sheet (I get the credits via index-match). When the A.S. radio button is selected, I need the course name lists to change to the corresponding A.S. tables (credits are obtained through index-match, as before). The course names are saved as named ranges, and the tables themselves all have unique names.

Any help much appreciated!

David

 

[ghrain22]

Well typically (atleast how I know how to do it). You would link your radio buttons to cells and then use conditionals to have it select the range to do. However, I would recommend avoiding the macroing and activex/form controls altogether. Why not create another drop down through data validation with your choices. And then create named ranges with the same name as your choices 2 choices. Then the range for your second drop down would just use an indirect function.
=Indirect(CellofChoicedropDown)

 

[k9renegade]

ghrain22, I tried this and it is not working. I believe what you are describing is where the second drop down list is dependent on the first drop down as Mike Girvin (ExcelisFun) used in Excel Magic Trick 900: Simulate Filter With Formula to Create Immediate Filter Results.

My main office has Excel 2010, but another office I go to is Excel 2003 (where I am currently working for this post). Does this trick work in 2003?







https://www.youtube.com/watch?v=pCAXRLYr59E&list=PL63A7644FE57C97F4&index=9