Select different lists

Sheet2 Column A has your "Option A" list starting in A1 on down
Sheet2 Column B has your "Option B" list starting in B1 on down

On the destination sheet, Cell A1 has the dropdown list with two options; Option A, Option B (case sensitive)
When you select from the dropdown list, the code will automatically copy the selected list to cell B1 on down.

To install the code:

• Right-click on the sheet tab that has the dropdown list
• Select View Code from the pop-up context menu.
• Paste the code from below in the worksheet's code module.

Please Login or Register  to view this content.

Or you can try it like this...

Please Login or Register  to view this content.

INDEX() has a little-know, and seldom-used 4th argument that will let you choose from different lists. This is basically what you can do - tweak it as needed...

	A	B	C	D	E	F
97		Range1	Range2	Range3
98	index from 3 different ranges	aa	123	123	567
99		bb	234	234	2	Range1
100		cc	345	345	567	Range2
101		dd	123	456	range2	Range3
102		ee	234	567	567
103		ff	345	678
104		gg	123	789
105		hh	234	890
106		ii	345	901
107		jj	123	999

E98=INDEX((B98:D100,B101:D103,B104:D107),2,3,E99)
E99=range selection

these are using named ranges...
E100
=INDEX((range1,range2,range3),2,3,E99)
E101=range selection

Finally an option to select the range name...
=INDEX((range1,range2,range3),2,3,MATCH(E101,F99:F101,0))