How to show Data dependent on Dropdown List from different tables

[checkmate]

I am new to Excel formulas. I have 3 list in 3 different columns in Sheet2 as below.

List1 List2 List3 1 1 1 2 2 2 3 3 3 4 4 4 5 5 5 6 6 7 7 8 9 10 <tbody> </tbody>
In sheet1, i want to create a dropdown list of the Listnames e.g. List1, List2, List3. Depending on the Listname selected, i should be able to populate the rows of that list in the rows below the list name. I don't know how to do it and Macros are also not enabled here. Please any help will be greatly appreciated.

<colgroup><col style="width:48pt" width="64" span="3"> </colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Welcome to Mr Excel

See
http://www.contextures.com/xlDataVal02.html

M.

 

[checkmate]

Thanks for the reply. I tried the link already, but in my case i want to populate whole list in the rows and not to show another list with the selection.

 

[Marcelo Branco]

Try

	A​	B​	C​	D​	E​
1​	Pick a List​		List1​	List2​	List3​
2​	List2​		1​	1​	1​
3​	1​		2​	2​	2​
4​	2​		3​	3​	3​
5​	3​		4​	4​	4​
6​	4​		5​	5​	5​
7​	5​			6​	6​
8​	6​			7​	7​
9​	7​				8​
10​					9​
11​					10​

<tbody>
</tbody>

Create the named ranges
List1: $C$2:$C$6
List2: $D$2:$D$8
List3: $E$:$E$11

Create the dropdown in A2

Formula in A3 copied down to 10 rows (size of the greatest list)
=IFERROR(INDEX(INDIRECT(A$2),ROWS(A$3:A3)),"")

Hope this helps

M.

 

[checkmate]

Thanks for the reply @Marcelo Branco. This is the output, i am looking for.
But since I have to put the formulas in so many fields, more than 120 row to be precise. Is there any way where i can define the range and the data populates in those rows after selecting the list name.

Try

	A​	B​	C​	D​	E​
1​	Pick a List​		List1​	List2​	List3​
2​	List2​		1​	1​	1​
3​	1​		2​	2​	2​
4​	2​		3​	3​	3​
5​	3​		4​	4​	4​
6​	4​		5​	5​	5​
7​	5​			6​	6​
8​	6​			7​	7​
9​	7​				8​
10​					9​
11​					10​

<tbody>
</tbody>

Create the named ranges
List1: $C$2:$C$6
List2: $D$2:$D$8
List3: $E$:$E$11

Create the dropdown in A2

Formula in A3 copied down to 10 rows (size of the greatest list)
=IFERROR(INDEX(INDIRECT(A$2),ROWS(A$3:A3)),"")

Hope this helps

M.