How do I use Excel to show a list from a sheet in a second column, dependant on the first column?

Copper Contributor

Hi - I have a drop down list in column A - lets say 1, 2, 3, 4. 

 

That list relates to one of 4 sheets of data, say sheet 1, sheet 2, etc

 

If I select 1, I want it to only show a list of data from sheet 1 in Column B, likewise select if I 2, then only show a list of data from sheet 2 in Column B.

 

Anyone know how I do this please?

 

 

4 Replies
Please attach your sample file with sample data and sample results.

@Moyna Good ,

If without cosmetic when

=CHOOSE($C$2,'Sheet 1'!$A$1:$A$4,'Sheet 2'!$A$1:$A$4,'Sheet 3'!$A$1:$A$4,'Sheet 4'!$A$1:$A$4)

in data validation

 

@Moyna Good 

In the attached file, these are the 3 choices of formulas for the drop-down list: 

1. Static categories and items using non-volatile CHOOSE: 

=CHOOSE(A2,

'Sheet 1'!$A$1:$A$4,

'Sheet 2'!$A$1:$A$4,

'Sheet 3'!$A$1:$A$4,

'Sheet 4'!$A$1:$A$4)

2. Dynamic categories and static items using volatile INDIRECT: 

=INDIRECT("'Sheet "&A3&"'!$A$1:$A$4")

3. Dynamic categories and items using non-volatile INDEX: 

=CountedItems

My choice is the last one. What about you?