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.

[TABLE="width: 192"]
<colgroup><col style="width:48pt" width="64" span="3"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"][TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD]List1
[/TD]
[TD]List2
[/TD]
[TD]List3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]

[/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"]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.
[/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
[TR]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Pick a List​

[/TD]
[TD][/TD]
[TD]

List1​

[/TD]
[TD]

List2​

[/TD]
[TD]

List3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD="bgcolor: #D9D9D9"]

List2​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

10​

[/TD]
[/TR]
</tbody>[/TABLE]


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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Pick a List​

[/TD]
[TD][/TD]
[TD]

List1​

[/TD]
[TD]

List2​

[/TD]
[TD]

List3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD="bgcolor: #D9D9D9"]

List2​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

9​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

10​

[/TD]
[/TR]
</tbody>[/TABLE]


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.