Populating combo box with different lists depending on Option Button selection

[HeyInKy]

I would like to have a Form Control Combo Box which is populated by 1 of three lists, controlled/selected by 3 option buttons. If the first option button is selected, the combo box will populate with List A, if the second button is selected, then it would populate with List B, and the 3rd button, List C. I do not want to use VBA.

Can this be done?

-HeyInKy

[HeyInKy]

Here's my partial solution...

The option buttons are linked to Sheet2 $D$1. On that sheet, I have list A in column A, list B in column B, and C in C, each beginning on row 2. Starting in D2, I have the formula:

Please Login or Register  to view this content.

This is then copied down to D81 and I have my combo box linked to the range D2:D81. This works, but I have 2 problems: list A has 80 rows (A2:A81), but list B is only 40 rows, and list C is only 10 rows. Problem 1 is the combo box is not displaying all 80 options if option button 1 is selected (List A with 80 items), and if option button 2 or 3 is selected, the blank cells are of course in the combo box and just displaying "0".

Hmmm... but it's a start! Any help on my fixing my solution or a completely different solution (no VBA) is welcomed!

[Tony Valko]

Try something like this...

In this example we have 3 lists:

Data Range

	A	B	C
1	List1	List2	List3
2	1	4	6
3	2	5	7
4	3		8
5			9
6			10

Each list is given a defined named range:

List1: =Sheet1!$A$2:$A$4
List2: =Sheet1!$B$2:$B$3
List3: =Sheet1!$C$2:$C$6

Then, create this defined named formula:

Goto the Formulas tab>Define name
Name: MyLists (or whatever valid name you want to use)
Refers to: =CHOOSE(Sheet2!$D$1,List1,List2,List3)

Then, for the combo box input range use: MyLists

[HeyInKy]

Worked like a charm!! THANK YOU!!!

-HeyInKy

[HeyInKy]

And just as a follow-up if anyone is interested:

My 3 option boxes link to $B$1, and my cell link for the combo box is $C$1, so to get the selected value, my formula in D1 ended up:

Please Login or Register  to view this content.

[Tony Valko]

Good deal. Thanks for the feedback!