Excel: Vary Input Range for Drop Down Menu with radio buttons

[MrStohler]

Is there a way to change the Input Range of a Combo box based on a radio button selection? If this goes well, the spreadsheet user will make a choice from several radio buttons. For each selection, I would like to reference a different data array.

Is there a way to "hide" the Combo Box if the user selects an option that does not require the menu. (This solution is less important to me. If possible, I could change the input range to a cell that states "No Input Required&quot

The Combo Box I am attempting to use is made from the Forms Toolbar (apparently this is different than the one the control toolbar, but that's a separate question).

If this involves complicated VBA it might be over my head (I'm a newbie to VBA).

Thanks for your help.

 

[KenWright]

Does it have to be a Combo box, ie have you looked at Data Validation?). Assuming the range of data you want to pull from is say H3:H30. You could have 4 sets of data that might apply depending on the radio button choice, and lets assume these are in I3:L30 - Head the columns up 1,2,3,4 in I2:L2 just for clarity. Now create 4 radio buttons and have them all link to say cell K1, so that depending on which button is pressed K1 will be either 1,2,3,4.

Assume the cell you want your selected data based on the radio button to appear is B1. In B1 do Data / Validation / Change Any value to List and then select H3:H30. Now, in H3 put the following formula:-

=INDEX(I4:L4,0,$C$1)

and copy down to H30.

Now whatever radio button they click on will change the offset range in the formulas that the Data Validation range picks it's values up from.

Regards
Ken.................

 

[MrStohler]

Thanks for your response, but my situation involves retrieving data for several mutually exclusive categories of products to be used in subsequent calculations. The radio buttons would choose the category and the drop down menu would list the products that fit that category (the number of products and the information required varies greatly with category).

Example: If radio buttons were used to select a type of car, the drop down menu should change to reflect the models available from that manufacturer.

In my situation, I use index functions embedded choose statements to show the appropriate data for the selected category from the radio button and specific item from the drop down menu.

MY SOLUTION; I assign a macro to each of the radio buttons. The macro defines a named range in the spreadsheet; the name of the range is the same for each of the macros even though each macro assigns that name to a different group of cells. At the drop down menu, I list the input range as the name of the range defined by the radio button macros. This way I do not need to change the input range of the drop down menu, its done beforehand.

Thanks for your input.